For this to work, you need SQL Server 2008.

We use temporary tables and table variables occasionally and sometimes need to fill those temporary objects with data from the results of a stored procedure or dynamically built query (executed with sp_ExecuteSQL).  This was a little tricky pre 2008 – as it turns out in SQL Server 2008 this can be done easily.

DECLARE @jobs TABLE
( jobcount int
)
INSERT
INTO @jobs
EXEC
sp_executesql
N’select 1 AS jobcount’
SELECT
*
FROM @jobs

Here, I define a table variable and then insert into this from the execution of a query. I then select from the variable to proove that data was populated. This could easily be a Stored Procedure.

I am sure you can find uses for this , I think this is another good example of the lesser known additions Microsoft has included in 2008.

---

Possibly related posts: (automatically generated)

• Temporary tables vs table variables
• Temp Tables and Table Variables: When To Use What And Why
• Tip of the Day : SQL Table Variables