Pass test data to table-valued parameter within SQL

Question

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

I know how to pass in data from C#. One of my four stored procs using table-valued parameters is not producing the expected results. I'd like to execute my proc from SQL server management studio for debugging purposes, but I am unable to find the correct syntax for doing so, if such a syntax even exists. I haven't found anything relevant in the docs.

My type table:

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

My stored 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 have 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 clash: nvarchar is incompatible with MyTypeTable

Answer 1

You can't use a temp table - you have to 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 either INSERT ... VALUES as shown above or INSERT ... SELECT if you have an existing table containing the data you care about)

Answer 2

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