创建，删除并在用户定义的函数中插入临时表 [英] Create, drop and insert a temp table in a user-defined function

问题描述

我试图按照我的要求创建一个功能。

但是，当创建或删除 #tempTable 时，它会给出错误：

在函数中无效使用副作用运算符'drop object'

我的理解是我们不能有 create ， drop 或在函数中对 #temptable 插入操作。

是否正确？

My 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'）不为空
 DROP TABLE #tempTable 
 
 SELECT ID，COUNT（余额）
 INTO #tempTable 
'余额'FROM Table1 
 
 INSERT @RT_ResultFunction 
 SELECT T1.ID ，T1，NAME，T2，Balance 
 FROM Table2 T1，
 #tempTable T2 
 WHERE T1.ID = T2.ID 
 
 RETURN 
 END 
  

解决方案

这是正确的 - 您不能产生副作用： / p>

从这里开始： http://msdn.microsoft.com/en-us/library/aa1750 85（v = sql.80）.aspx

BEGIN ... END块中的语句不能有任何边效果。
函数副作用是对
资源状态的任何永久性更改，该资源具有函数外的作用域，例如对数据库表进行
修改。函数中的
语句所做的唯一更改是对
函数本地的对象（如本地游标或变量）的更改。对
数据库表的修改，对
函数不是本地的游标的操作，发送电子邮件，尝试修改目录以及
生成返回给用户的结果集

你会发现，即使没有你的 DROP 语句，是任何访问临时表的尝试都会给你消息（比如 SELECT ... INTO #TMP ）：
$ b

无法从函数内访问临时表

As @Dems指出，你可以使用表变量。因为这些都是变量，所以它们被限制在函数中，因此不会产生副作用。

您的函数可以运行为：

  ... 
 
 BEGIN 
 DECLARE @tempTable表（id varchar（20），rows int）
 
 insert @tempTable 
 SELECT ID，COUNT（余额）
 FROM Table1 
 
 INSERT @RT_ResultFunction 
 SELECT T1.ID，T1，NAME，T2，Balance 
 FROM Table2 T1，
 @tempTable T2 
 WHERE T1.ID = T2.ID 
 
 RETURN END 
  

未经测试或任何其他事物，但您获得了要点。

I am trying to create a function as per my requirements.

But, when am creating or drop #tempTable, it is giving an error as:

invalid use of a side-effecting operator 'drop object' within a function

My understanding is that we can't have create, drop or insert operations on #temptable in a function.

Is that correct?

My 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

解决方案

That is correct - you cannot have side effecting statements:

From here: 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.

What you would find, even without your DROP statement, is that any attempt to access a temp table will give you the message (such as a SELECT ... INTO #TMP):

Cannot access temporary tables from within a function

As @Dems points out, you can use table variables. Because these are variables, they are scoped within the function, and therefore aren't side effecting.

Your function might run as:

...

BEGIN
    DECLARE @tempTable table (id varchar(20), rows int)

    insert @tempTable
    SELECT Id, COUNT(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

Not tested or anything, but you get the gist.

这篇关于创建，删除并在用户定义的函数中插入临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！