Pass a TABLE variable to sp_executesql

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 开发者_如何学Gothe 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!

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.

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

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

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

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