Pass A User-Defined Table to a Stored Procedure

Question

I have a User Defined Table that I am passing into a stored procedure from within a stored procedure.

DECLARE @tmpInput MyTableType;

--Table is populated from an INPUT XML

exec ValidateInputXML SELECT * FROM @tmpInput TI WHERE TI.EntryType = 'Attribute';

Now this isn't giving me an error, but when I run a select from with the ValidateInputXML the table has no data.

Answer 1

You can also use Table-Valued parameter for your stored procedure. E.g.

/* Create a table type. */
CREATE TYPE MyTableType AS TABLE 
( Column1 VARCHAR(50)
, ........ );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. ValidateInputXML
    @TVP MyTableType READONLY
    AS 
     -- Do what ever you want to do with the table received from caller
    GO

/* Declare a variable that references the type. */
DECLARE @myTable AS MyTableType;

-- Fill @myTable with data and send it to SP. 
insert into @myTable SELECT * FROM @tmpInput TI WHERE TI.EntryType = 'Attribute';


/* Pass the table variable data to a stored procedure. */
EXEC ValidateInputXML @myTable ;
GO