THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Things Mike Learned

Mike partially hit the nail on the head in his comment on my last post. He inferred that you cannot use a table variable for a dynamically executed procedure because the variable, like any other variable would do, goes out of scope. Well, the real truth is that you cannot use EXECUTE to insert into table variable, period. This is documented in SQL Server Books Online (see the remarks section at http://msdn.microsoft.com/library/en-us/tsqlref/ts_ta-tz_7ysl.asp for more details).

So what's the answer? Use a temporary table (you know, #table_name) instead:

CREATE TABLE #OneValue (Val Varchar(11))
DECLARE @sExec Varchar (1000)

SET @sExec = 'SELECT au_id FROM authors where au_lname = ''MacFeather'''

INSERT INTO #OneValue
EXECUTE (@sExec)

SELECT * FROM #OneValue

DROP TABLE #OneValue

Published Monday, June 26, 2006 12:59 PM by Peter W. DeBetta

Comments

No Comments
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement