Clever Geek Handbook

Pass test data to table parameter in SQL

Is it possible, and if so, how to pass data to a table parameter of a stored function using SQL EXEC?

I know how to transfer data from C #. One of my four stored processes using table parameters is not giving the expected results. I would like to execute my proc from SQL Server Management Studio for debugging purposes, but I cannot find the correct syntax to do this, if such a syntax even exists. I haven't found anything suitable in the docs.

My type table:

CREATE TYPE [MyNameSpace].[MyTypeTable] AS TABLE( 
//... all my fields
)

My saved proc:

//... bunch of stuff
ALTER PROCEDURE [MyNameSpace].[MyStoredProc]
@MyTypeTableVar MyTypeTable READONLY 
AS
BEGIN
//Do a bunch of stuff
//Want to test the stuff in here
END

I tried:

IF OBJECT_ID('tempdb.dbo.#MyTempTable') IS NOT NULL DROP TABLE tempdb.dbo.#MyTempTable;
select top 0 *
into #MyTempTable
//existing table with structure that matches the table-valued param
from MyNameSpace.MyTable;

//...Long insert statement assigning test data to #MyTempTable

EXECUTE MyNameSpace.MyStoredProc @MyTypeTableVar = #MyTempTable;

which throws:

Operand type collision: nvarchar is incompatible with MyTypeTable

+3


source to share


2 answers


You cannot use a temporary table - you must use a table variable:

declare @t [MyNameSpace].[MyTypeTable]
insert into @t (/*columns*/) values
(/* first row */),
(/* second row */)

EXECUTE MyNameSpace.MyStoredProc @MyTypeTableVar = @t;


(You can populate it with INSERT ... VALUES

as shown above, or INSERT ... SELECT

if you have an existing table containing the data you are interested in)

+6


source


Here's a working example:



-- Declare a table parameter
DECLARE @registryUpdates AS typ_KeyValuePairStringTable;
-- Insert one row
INSERT INTO @registryUpdates 
VALUES ('Hello', 'World');
-- Call Stored Procedure
EXEC prc_UpdateRegistry @registryUpdates
+1


source






More articles:


All Articles