How to pass a temp table as a parameter into a separate stored procedure

Question

I have a stored procedure that takes an input parameter @CategoryKeys varchar, and parses its contents into a temp table, #CategoryKeys.

        -- create the needed temp table.         CREATE TABLE #CategoryKeys           (              CategoryKey SMALLINT           );          -- fill the temp table if necessary         IF Len(rtrim(ltrim(@CategoryKeys))) > 0           BEGIN               INSERT INTO #CategoryKeys                           (CategoryKey)               SELECT value               FROM   dbo.String_To_SmallInt_Table(@CategoryKeys, ',');           END 

If the temp table has rows, I would like to pass the table into a separate stored procedure. How would I go about creating a parameter in the separate procedure to hold the temp table?

Answer 1

While understanding scoping addresses the direct need, thought it might be useful to add a few more options to the mix to elaborate on the suggestions from the comments.

1. Pass XML into the stored procedure
2. Pass a table-valued parameter into the stored procedure

1. Pass XML into the stored procedure

With XML passed into a parameter, you can use the XML directly in your SQL queries and join/apply to other tables:

CREATE PROC sp_PassXml     @Xml XML AS BEGIN     SET NOCOUNT ON     SELECT T.Node.value('.', 'int') AS [Key]     FROM @Xml.nodes('/keys/key') T (Node) END GO 

Then a call to the stored procedure for testing:

DECLARE @Text XML = '<keys><key>1</key><key>2</key></keys>' EXEC sp_PassXml @Text 

Sample output of a simple query.

Key ----------- 1 2 

2. Pass a table-valued parameter into the stored procedure

First, you have to define the user defined type for the table variable to be used by the stored procedure.

CREATE TYPE KeyTable AS TABLE ([Key] INT) 

Then, you can use that type as a parameter for the stored proc (the READONLY is required since only IN is supported and the table cannot be changed)

CREATE PROC sp_PassTable     @Keys KeyTable READONLY AS BEGIN     SET NOCOUNT ON     SELECT * FROM @Keys END GO 

The stored proc can then be called with a table variable directly from SQL.

DECLARE @Keys KeyTable INSERT @Keys VALUES (1), (2) EXEC sp_PassTable @Keys 

Note: If you are using .NET, then you can pass the SQL parameter from a DataTable type matching the user defined type.

Sample output from the query:

Key ----------- 1 2