Pass a TABLE variable to sp_executesql

Question

I'm trying to pass a TABLE variable to the sp_executesql procedure:

 DECLARE @params NVARCHAR(MAX)  SET @params = '@workingData TABLE ( col1 VARCHAR(20),                 col2 VARCHAR(50) )'   EXEC sp_executesql @sql, @params, @workingData 

I get the error:

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'TABLE'. 

I tried omitting the column specification after 'TABLE'. I also tried to declare the table as a variable inside the dynamic SQL. But no luck...

Seems to me that TABLE variables aren't allowed to be passed as parameters in this procedure?. BTW: I'm running MSSQL2008 R2.

I'm not interested in using a local temp table like #workingData because I load the working data from another procedure:

INSERT INTO @workingData      EXEC myProc @param1, @param2 

Which I cannot do directly into a temp varaible (right?)...

Any help appreciated!

Answer 1

If you are using SQL Server 2008, to pass a table variable to a stored procedure you must first define the table type, e.g.:

CREATE TYPE SalesHistoryTableType AS TABLE (                          [Product] [varchar](10) NULL,                     [SaleDate] [datetime] NULL,                     [SalePrice] [money] NULL ) GO 

or use an existing table type stored in the database.

Use this query to locate existing table types

SELECT * FROM sys.table_types 

To use in an stored procedure, declare an input variable to be the table:

CREATE PROCEDURE usp_myproc (     @TableVariable SalesHistoryTableType READONLY ) AS BEGIN     --Do stuff       END GO 

Populate the table variable before passing to the stored procedure:

DECLARE @DataTable AS SalesHistoryTableType INSERT INTO @DataTable SELECT * FROM (Some data) 

Call the stored procedure:

EXECUTE usp_myproc @TableVariable = @DataTable 

Further discussions here.

Answer 2

OK, this will get me what I want, but surely isn't pretty:

DECLARE @workingData TABLE ( col1 VARCHAR(20),         col2 VARCHAR(20) )      INSERT INTO @workingData         EXEC myProc      /* Unfortunately table variables are outside scope        for the dynamic SQL later run. We copy the         table to a temp table.         The table variable is needed to extract data directly        from the strored procedure call above...     */     SELECT *      INTO #workingData     FROM @workingData           DECLARE @sql NVARCHAR(MAX)     SET @sql = 'SELECT * FROM #workingData'      EXEC sp_executesql @sql 

There must be a better way to pass this temporary resultset into sp_executesql!?

Regards Alex