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!

What is the content of all of your variables? I don't think this is doing what you're expecting at all. — Donnie, Nov 23 '10 at 17:38
I use the @workingData TABLE to store the results from another procedure which returns a general resultset used by several procedures. This is to reduce redundant code: INSERT INTO @workingData EXEC someProc — Alex, Nov 24 '10 at 07:52
this link might help http://stackoverflow.com/questions/7329996/using-table-variable-with-sp-executesql — , Mar 14 '16 at 07:32

score 12 · Answer 1 · edited Nov 23 '15 at 15:52

12

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.

edited Nov 23 '15 at 15:52

bluish

23,093
23
110
171

answered Jan 01 '11 at 17:10

Gary Kindel

15,752
7
44
65

1

+1 It assumes he is not trying to make changes to the table variable that will be visible in the calling scope though I think? – Martin Smith Jan 01 '11 at 17:39
I need this to work with dynamic SQL passed into so_executeSql. I tried to declare the parameter as a table type in the @params specification-parameter above. I'm not calling a stored procedure directly. . . – Alex Jan 02 '11 at 08:01
The link you gave doesn't work anymore. Is there another resource available? – takrl Jun 17 '14 at 10:51

score 3 · Answer 2 · answered Nov 24 '10 at 07:58

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

Downvoted this answer just because it's needless to copy the data between the tables. You can insert directly into the temp table instead (which I'm sure you know long ago, but for the sake of SO, this answer is poor). — bambams, Jun 16 '17 at 17:37

score 2 · Answer 3 · answered Nov 25 '11 at 18:20

2

While this may not directly answer your question, it should solve your issue overall.

You can indeed capture the results of a Stored Procedure execution into a temporary table:

INSERT INTO #workingData
EXEC myProc

So change your code to look like the following:

CREATE TABLE #workingData ( col1 VARCHAR(20),    
    col2 VARCHAR(20) )    

INSERT INTO #workingData    
    EXEC myProc    

DECLARE @sql NVARCHAR(MAX)    
SET @sql = 'SELECT * FROM #workingData'    

EXEC sp_executesql @sql

Regards, Tim

answered Nov 25 '11 at 18:20

Tim Friesen

335
1
11

One thing to keep in mind is that the data types within the table definition and the resultset returned from the SP must match. – Tim Friesen Nov 25 '11 at 18:22
Just realized that I might be about a year late on this answer. :) Sorry! – Tim Friesen Nov 25 '11 at 18:28
That's temp table, not table variable. – gotqn Aug 28 '13 at 14:38
@gotqn The OP mentioned that they wanted to use a table variable because they thought they could not insert into a temporary table/variable, which is why I provided this as a possible solutions, since it is indeed possible when using temp tables. – Tim Friesen Aug 29 '13 at 16:11

score 0 · Answer 4 · answered May 16 '12 at 04:32

Alter PROCEDURE sp_table_getcount 
 @tblname nvarchar(50) ,
 @totalrow int output 
AS
BEGIN

Declare @params nvarchar(1000)
Declare @sql nvarchar(1000)
set @sql = N'Select @cnt= count(*) From @tbl'
set @params = N'@tbl nvarchar(50) , @cnt int OUTPUT'
Exec sp_executesql @sql , @params ,@tbl=@tblname ,  @cnt = @totalrow OUTPUT   
END
GO

Please note that the above code will not work as table as a object is out of the scope.It will give you the error: must declare table variable.In order to work around we can do the following.

Alter PROCEDURE sp_table_getcount 
 @tblname nvarchar(50) ,
 @totalrow int output 
AS
BEGIN

Declare @params nvarchar(1000)
Declare @sql nvarchar(1000)
set @sql = N'Select @cnt= count(*) From dbo.' + @tblname
set @params = N'@cnt int OUTPUT'
Exec sp_executesql @sql , @params , @cnt = @totalrow OUTPUT   
END
GO

score 0 · Answer 5 · answered Jun 29 '20 at 23:19

So-called TableType is tricky. @Alex version should work. However, to simplify and faster performance, go check sys.tables for matching table name while not compromise security and performance.

Here it is

create proc [dbo].Test11 
@t1 AS nvarchar(250), @t2 nvarchar(250)
AS 
BEGIN
SET nocount ON; 
DECLARE @query AS nvarchar(MAX)
if exists (select * from sys.tables where name = @t1) and 
    exists (select * from sys.tables where name = @t2)
begin
    SET @query = N'select * FROM '+ @t1 + N' join ' + @t2 + N' ON ...' ;
    select 'Safe and fast'
    print @query
    exec sp_executesql @query

end
else
    select 'Bad, no way Jose.'

SET nocount OFF; 
END
GO

Pass a TABLE variable to sp_executesql

5 Answers5

Linked