Geek asks and answers

Dynamically named temporary table returns "invalid object name" when referenced in a stored procedure

When I run the following code, I get the error "invalid object name", any idea why?

I need to create a dynamically named temporary table that will be used in a stored procedure.

 DECLARE @SQL NVARCHAR(MAX)
 DECLARE @SessionID NVARCHAR(50)
 SET @SessionID = 'tmp5l7g9q3l1h1n5s4k9k7e'
 ;
 SET        
 @SQL = N'      CREATE TABLE #' + @SessionID + ' ' +
         N'     (' +
    N'      CustomerNo NVARCHAR(5), ' +
    N'      Product NVARCHAR(3), ' + 
    N'      Gross DECIMAL(18,8) ' +
    N'      )'
 ;
 EXECUTE sp_executesql @SQL
 ;
 SET        
 @SQL = N'      SELECT * FROM #' + @SessionID
 ;
 EXECUTE sp_executesql @SQL

Thank!

+3
source share
3 answers

You are doing it wrong!

Try:

exec(@SQL)

instead:

EXECUTE sp_executesql @SQL

To use sp_executesql, the variable must be inside @SessionIDquotation marks and must be provided with an input parameter. this for a complete example!

You should know that Dynamic SQL is a good port for SQL injection!

+1
source

?. SQL Server :

SQL Server

:

, Engine Engine , . , , . , #MyTempTable, TempDB, , CREATE TABLE . - , 116 .

, , TempDB , . sp_help , TempDB.

USE TempDB
go
execute sp_Help #mytemp

TempDB swithching .

SELECT name, create_date FROM TempDB.sys.tables WHERE name LIKE '#%'
+3

This syntax works

CREATE TABLE #SessionID (CustomerNo NVARCHAR(5),  Product NVARCHAR(3),  Gross DECIMAL(18,8));
Select COUNT(*) from #SessionID;
Drop Table #SessionID;
+1
source

More articles:


All Articles