sql - 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