EXECUTE sp_executesql

Question

i have a problem with EXECUTE sp_executesql and stuck with it. my query is as follows

   DECLARE @TABLE_NAME VARCHAR(5)='#T'
   DECLARE @TABLE_ID INTEGER=0
   DECLARE @QRY NVARCHAR(MAX) =''
   DECLARE @TABLE VARCHAR(3)
 DECLARE CRSR_WAGES CURSOR FOR SELECT Code,Name , ROW_NUMBER() OVER (ORDER BY U_Prrty, DocEntry) Slno, COUNT(Code) OVER (PARTITION BY OBJECT) ColNos
                                     FROM  [@CCS_WAGE] 
                                     WHERE  U_DISPP = 'Y' AND U_DEDWT='E'                        
OPEN CRSR_WAGES
FETCH NEXT FROM CRSR_WAGES INTO @WAGECOD,@WAGE_DESC,@SI_NO,@COL_NOS
WHILE @@FETCH_STATUS=0
BEGIN
    SET @TABLE=@TABLE_NAME+CONVERT(VARCHAR(2),@TABLE_ID)
    SELECT @TABLE AS 'TABLE'
    SET @QRY='(SELECT empno, amt AS '+'['+@WAGE_DESC+']'
    SET @QRY=@QRY+' INTO '+@TABLE
    SET @QRY=@QRY+' FROM CCS_PYMONTHRESULT WHERE wgcod='''+@WAGECOD+''')'
    SELECT @QRY
    EXECUTE sp_executesql @QRY
    SET @TABLE_ID=@TABLE_ID+1
    DECLARE @STAR AS NVARCHAR(50)='SELECT * FROM ' + @TABLE
    SELECT @STAR
    EXECUTE sp_executesql @STAR
    FETCH NEXT FROM CRSR_WAGES INTO @WAGECOD,@WAGE_DESC,@SI_NO,@COL_NOS                    
        END
CLOSE CRSR_WAGES
DEALLOCATE CRSR_WAGES

but the problem is when execute this error message comes like this Invalid object name '#T0'. Invalid object name '#T1'. Invalid object name '#T2'.

score 3 · Accepted Answer · answered Sep 10 '12 at 06:31

Assuming that the temporary tables that you are referencing exists, then your problem is session scope. Temporary tables marked with a single # are visible to the session that they are created in, they are also known as local temporary tables.

The problem that you have is that when you execute a piece of dynamic sql it runs in a new session - hence that session cannot see the temporary tables that you have created.

To fix the problem you could do one of 3 things:

Create the temporary table within your dynamic sql
Use a global temporary table using ## as these are visible across all sessions
Re-design your code to insert into a table variable and return that as an output parameter to sp_executesql

I hope this helps you.

score 2 · Answer 2 · answered Sep 10 '12 at 07:30

Dynamic SQL runs in the same session as the calling code. The issue is not session, but scope. Your dynamic SQL creates the temporary tables inside the sp_executesql call and thus the created #temp table is only visible within that sp_executesql call, as documented in MSDN:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

So you need retain of the #temp table in a scope that covers both the creation and the use, for example (also fixed the SQL injection problems you had and pass @WAGECODE as a parameter):

SET @TABLE=@TABLE_NAME+CONVERT(VARCHAR(2),@TABLE_ID);
SET @QRY ='(SELECT empno, amt AS '+ QUOTENAME(@WAGE_DESC);
SET @QRY +=' INTO '+@TABLE;
SET @QRY +=' FROM CCS_PYMONTHRESULT WHERE wgcod=@WAGECOD)';
SET @QRY += '
          SELECT * FROM ' + @TABLE;
EXECUTE sp_executesql @QRY, N'@WAGECODE <typeof@wagecode>', @WAGECODE;

Needless to say, this is quite a unnecessary use of #temp tables, you could simple select the result you want and call ti a day, there is no need for #temp tables:

SET @QRY ='(SELECT empno, amt AS '+ QUOTENAME(@WAGE_DESC);
SET @QRY +=' FROM CCS_PYMONTHRESULT WHERE wgcod=@WAGECOD)';
EXECUTE sp_executesql @QRY, N'@WAGECODE <typeof@wagecode>', @WAGECODE;

EXECUTE sp_executesql

2 Answers2

Linked