Create, drop and insert a temp table in a user-defined function

Question

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

Bit confused about your SQL. How to you select from #tempTable after you've dropped it - its never recreated. — Jon Egerton
– Jon Egerton, Commented Jun 29, 2012 at 19:31
If you want help solving this issue, please explain the point of the #temptable inside the function. Also what to do with the Id parameter. — Aaron Bertrand
– Aaron Bertrand, Commented Jun 29, 2012 at 20:45
Please stop using implicit joins. They are a SQl antipattern. — HLGEM
– HLGEM, Commented Jun 29, 2012 at 21:11

Jon Egerton · Accepted Answer · 2012-06-29 20:55:25Z

31

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.

edited Jun 29, 2012 at 20:55

answered Jun 29, 2012 at 19:34

Jon Egerton

41.8k11 gold badges100 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MatBailie Over a year ago

But you can use table variables. Which would be a useful fact to add to your answer to assist the OP in resolving his problem (rather than just knowing why it's a problem) ;)

Luke Marlin Over a year ago

After I read the MSDN Documentation, I asked myself : is it possible to modify real data from a table (INSERT/UPDATE/DELETE) by calling a Stored Procedure in your function ? I suppose that you can't, but I'd rather be sure that an error will be produced. Could you tell me if you know that ? :)

Mike D. Over a year ago

@LukeMarlin You can't call stored procedures from a UDF because stored procedures CAN cause side effects and functions are not allowed to.

Aaron Bertrand · Accepted Answer · 2012-06-29 21:02:08Z

2

I have no idea why you need a #temp table in this function or why it's a multi-statement TVF in the first place. The following will be much more efficient (though I don't understand the purpose of the @Id parameter):

CREATE FUNCTION [dbo].[RT_ResultFunction]
(
    @Id VARCHAR(4000)
)
RETURNS TABLE
WITH SCHEMABINDING 
AS
  RETURN 
  (
    SELECT T2.ID, T2.NAME, T1.Balance
    FROM
    (
      SELECT ID, Balance = COUNT(Balance) 
        FROM dbo.Table1 
        GROUP BY ID
    ) AS T1
    INNER JOIN dbo.Table2 AS T2
    ON T1.ID = T2.ID
  );

As Jon pointed out, I think it could also be re-written as follows, and it's actually how I started writing it, but I have no way to confirm if either of these actually returns the data you're trying to return:

CREATE FUNCTION [dbo].[RT_ResultFunction]
(
    @Id VARCHAR(4000)
)
RETURNS TABLE
WITH SCHEMABINDING 
AS
  RETURN 
  (
    SELECT T2.ID, T2.NAME, Balance = COUNT(T1.Balance)
    FROM dbo.Table1 AS T1 
    INNER JOIN dbo.Table2 AS T2
    ON T1.ID = T2.ID
    GROUP BY T2.ID, T2.NAME
  );

edited Jun 29, 2012 at 21:02

answered Jun 29, 2012 at 20:49

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

2 Comments

Jon Egerton Over a year ago

True - the temp table is a bit of a "luxury" in this case, but its a useful point to cover in any case. (also you need an alias on the inner count col).

Aaron Bertrand Over a year ago

@Jon ID and Name come from different tables. I don't know the schema or the data so I tried to remain true to the original query.

user3231262 · Accepted Answer · 2014-01-24 08:51:03Z

-2

Remove # instead use @ I have not tested other aspect

answered Jan 24, 2014 at 8:51

user3231262

71 bronze badge

Collectives™ on Stack Overflow

Create, drop and insert a temp table in a user-defined function

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related