Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2010-04-06 : 14:22:37
|
Question? How can I query a table variable or temporarily table in dynamic sql? Specifically, I wish to take a table variable or temporarily table that exists in the scope of a stored procedure and query said table inside a dynamic sql statement. I would appreciate if someone can point me in the right direction. Thank you for your help!
Update: I looked at the link here,
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2
but I'd prefer table variables and the query I'm using is not complying. You can specify table variables inside dynamic sql, right, under the notion that the table variable has been created outside its scope? |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-06 : 14:52:36
|
Use a temporary table created with a CREATE TABLE #tableName statement, then just reference it with #tableName within the dynamic SQL.
CREATE PROCEDURE dbo.Test
AS
DECLARE @SQL VARCHAR(255)
SET @SQL = 'SELECT * FROM #tmp'
CREATE TABLE #tmp (id INT)
INSERT INTO #tmp SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
EXEC (@SQL)
DROP TABLE #tmp
GO
EXEC dbo.Test
There are 10 types of people in the world, those that understand binary, and those that don't. |
 |
|
|
|
|
|
How do I specify table variable in dynamic sql?