Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks Mr.Brownstone...i created ## table and the problem get solved. –  Sivajith Sep 10 '12 at 6:52
    
one more doubt... as in my query my column name is dynamic and how can i choose that through a query..in this case i need second column of all ## tables –  Sivajith Sep 10 '12 at 6:56
    
If you have the table name you could do something like SELECT name FROM tempdb.sys.columns WHERE object_id = OBJECT_ID(N'table_name') AND column_id = 2; –  Mr.Brownstone Sep 10 '12 at 7:03
    
A good alternative to temp table is defining it as CTE. It is very flexible and a great substitute for temp tables. Have you a look at the link below to see how to define CTE : codeproject.com/Articles/265371/… –  Nazila Beikpour Sep 10 '12 at 7:12
add comment

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.