How do I specify table variable in dynamic sql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How do I specify table variable in dynamic sql?

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources