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?

Instead of passing a comma-separated list in the first place, have you thought about using [table-valued parameters](http://technet.microsoft.com/en-us/library/bb510489.aspx)? Also have you read [this article](http://www.sommarskog.se/share_data.html)? — Aaron Bertrand, Nov 20 '13 at 19:47

score 32 · Answer 1 · answered Nov 28 '15 at 05:09

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.

Pass XML into the stored procedure
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

While I have the accepted answer to the question, I am upvoting this because "just because the scope works".....doesn't mean its a good solution. Treating #tempTables like a global variable is bad routine practice. And I do exactly this, I pass xml all the time to promote set based operations to avoid cursors and RBAR. — granadaCoder, Feb 15 '18 at 15:24
this should be the accepted answer: even if it is not directly answering the question — whytheq, Sep 09 '19 at 12:32

granadaCoder · Accepted Answer · 2022-05-23T11:57:26.947

When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.

Below is a sample:

IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc002'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspProc002]
END


GO

CREATE Procedure dbo.uspProc002 
AS

BEGIN

/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
        THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;  
end
*/

    /* Note, I did not Create #TableOne in this procedure.  It "pre-existed".  An if check will ensure that it is there.  */
    IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
    begin
        Insert into #TableOne ( SurrogateKey , NameOf ) select 2001, 'Hello-From-uspProc002'
    end

END


GO



IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc001'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspProc001]
END


GO

CREATE Procedure dbo.uspProc001 (
@Param1 int
)
AS

BEGIN


    IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
    begin
            drop table #TableOne
    end


    CREATE TABLE #TableOne
    ( 
    SurrogateKey int , 
    NameOf varchar(12)
    )

    Insert into #TableOne ( SurrogateKey , NameOf ) select 1001, 'Hello-From-uspProc001'

    Select 'before-nested-call' as MyStatus1, * from #TableOne

    EXEC dbo.uspProc002 

    Select 'after-nested-call' as MyStatus1, * from #TableOne

    IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
    begin
            drop table #TableOne
    end


END


GO




exec dbo.uspProc001 0

HAVING SAID THAT, PLEASE DO NOT CODE UP ALOT OF THESE. ITS THE SQL EQUIVALENT OF A GLOBAL VARIABLE AND IT IS DIFFICULT TO MAINTAIN AND BUG PRONE.

score 1 · Answer 3 · answered Jan 10 '20 at 04:44

Stored proc that uses temp table

CREATE OR ALTER Procedure Engine.TestProcTempTable
AS
BEGIN
  --DROP TABLE IF EXISTS #TestProcTempTable ;
  SELECT * from #TestProcTempTable;
END

Create put data in to temp table which will be used by SP

DROP TABLE IF EXISTS #TestProcTempTable ;
select * into #TestProcTempTable from <TABLE_NAME>;
execute Engine.TestProcTempTable

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

3 Answers3

Linked

Related