CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Shachaf.Gortler
Shachaf.Gortler

Reputation: 5735

Passing temp table from one execution to another

I want to pass a temp table from one execution path to another one nested in side it

What I have tried is this:

    DECLARE @SQLQuery AS NVARCHAR(MAX)

    SET @SQLQuery = ' 
    --populate #tempTable with values   
    EXECUTE('SELECT TOP   (100)    * FROM ' + tempdb..#tempTable) 

    EXECUTE sp_executesql @SQLQuery

but it fails with this error message:

Incorrect syntax near 'tempdb'

Is there a another\better way to pass temporary table between execution contexts?

Upvotes: 1

Views: 1948

Answers (4)

E. Monk
E. Monk

Reputation: 378

Your quotations are messed up. Try:

  SET @SQLQuery=' 
    --populate #tempTable with values   
    EXECUTE(''SELECT TOP   100    * FROM '' + tempdb..#tempTable + '') '

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

The reason for your syntax error is that you are doing an unnecessary EXECUTE inside an EXECUTE, and you didn't escape the nested single-quote. This would be the correct way to write it:

SET @SQLQuery=' 
--populate #tempTable with values   
SELECT TOP 100 * FROM tempdb..#tempTable'

However, I have a feeling that the syntax error is only the beginning of your problems. Impossible to tell what you're ultimately trying to do here, only seeing this much of the code, though.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Your temp table will be visible inside the dynamic sql with no problem. I am not sure if you are creating the temp table inside the dynamic sql or before.

Here it is with the table created BEFORE the dynamic sql.

create table #Temp(SomeValue varchar(10))
insert #Temp select 'made it'

exec sp_executesql N'select * from #Temp'

Upvotes: 1

Cahit
Cahit

Reputation: 2534

You can create a global temp table using the ##tablename syntax (double hash). The difference is explained on the TechNet site:

There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.

For example, if you create the table employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If a database session creates the local temporary table #employees, only the session can work with the table, and it is deleted when the session disconnects. If you create the global temporary table ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.

If a temporary table is created with a named constraint and the temporary table is created within the scope of a user-defined transaction, only one user at a time can execute the statement that creates the temp table. For example, if a stored procedure creates a temporary table with a named primary key constraint, the stored procedure cannot be executed simultaneously by multiple users.

The next suggestion may be even more helpful:

Many uses of temporary tables can be replaced with variables that have the table data type. For more information about using table variables, see table (Transact-SQL).

Upvotes: 2

Related Questions