Passing a temp table to a stored procedure

Posted by Adam Rink at 7/5/2009
Categories: Performance,Queries
Tags: Stored Procedure Temp Table
With SQL 2008, table variables can now be passed into stored procedures.  Temp tables can also be passed into stored procedures from other stored procedures and triggers.  This is true in 2008 and earlier versions like SQL 2005 and 2000.   This allows for cleaner code as many lined stored procedures can now be broken up into multiple sub-procedures. 

A child stored procedure can see its parent's temp table. This child procedure can INSERT, DELETE and UPDATE rows just like a normal temp table.  When the child returns to the parent, the data is changed and can be passed into more child stored procedures.

Using a temp table and passing it to another procedure, in most cases, will be faster than looping through a result set with a cursor and calling a stored procedure with parameters. 

An example:

CREATE PROC dbo.udfParent
AS
BEGIN

    SELECT 1 AS MyInt,
    'Hello' AS MyChar
    INTO #myTemp

    EXEC dbo.udfChild

    SELECT *
    FROM #myTemp

    DROP TABLE #myTemp

    RETURN (0)
END

GO

CREATE PROC dbo.udfChild
AS
BEGIN

    SELECT *
    FROM #myTemp

    UPDATE #myTemp
    SET MyInt = 2

    RETURN (0)
END

GO

EXEC dbo.udfParent

 

What did you think of this article?




Trackbacks
Trackback specific URL for this entry
  • No trackbacks exist for this entry.
Comments
Display comments as (Linear | Threaded)
Page: 1 of 2
Page: 1 of 2
Leave a comment

 Enter the above security code (required)

 Name

 Email (will not be published)

 Website

Your comment is 0 characters limited to 3000 characters.