Peter DeBetta's blog about programming in SQL Server 2008, 2005, etc. using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.
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
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About Peter DeBetta
Peter DeBetta is an independent consultant specializing in design, development, implementation, and deployment of Microsoft SQL Server, Microsoft SharePoint Server, and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!
Peter is a Microsoft MVP for SQL Server, an MCP, President of the
North Texas SQL Server User Group, and a member of PASS.
When Peter isn’t working, you can find him singing and playing guitar (click
here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.