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!
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.