但是,当我创建或删除#tempTable时,它会给出一个错误：
invalid use of a side-effecting operator ‘drop object’ within a function
我的理解是我们不能在函数中的#temptable上创建,删除或插入操作.
那是对的吗？
我的sql：
CREATE FUNCTION [dbo].[RT_ResultFunction]
(
Id VARCHAR(4000)
)
RETURNS @RT_ResultFunction TABLE
(
Id VARCHAR(20),Name varchar(20),Balance Int
)
AS
BEGIN
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
DROP TABLE #tempTable
SELECT Id,COUNT(Balance)
INTO #tempTable
'Balance' FROM Table1
INSERT @RT_ResultFunction
SELECT T1.ID,T1,NAME,T2,Balance
FROM Table2 T1,#tempTable T2
WHERE T1.ID = T2.ID
RETURN
END
解决方法
从这里：http://msdn.microsoft.com/en-us/library/aa175085(v=sql.80).aspx
The statements in a BEGIN…END block cannot have any side effects.
Function side effects are any permanent changes to the state of a
resource that has a scope outside the function such as a modification
to a database table. The only changes that can be made by the
statements in the function are changes to objects local to the
function,such as local cursors or variables. Modifications to
database tables,operations on cursors that are not local to the
function,sending e-mail,attempting a catalog modification,and
generating a result set that is returned to the user are examples of
actions that cannot be performed in a function.
即使没有DROP语句,您会发现任何尝试访问临时表都会给您提供消息(例如SELECT … INTO #TMP)：
Cannot access temporary tables from within a function
正如@Dems指出的那样,您可以使用表变量.因为这些是变量,所以它们在函数范围内,因此不会产生副作用.
您的函数可能运行如下：
...
BEGIN
DECLARE @tempTable table (id varchar(20),rows int)
insert @tempTable
SELECT Id,COUNT(Balance)
FROM Table1
INSERT @RT_ResultFunction
SELECT T1.ID,@tempTable T2
WHERE T1.ID = T2.ID
RETURN END
没有测试或任何东西,但你得到了要点.
