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

Answer 1

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.

Answer 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
  );