Using Temp Tables across Stored Procedures

I often come across a situation where it is hugely beneficial to create a temporary table, populate it with necessary information and then have another stored procedure use this temporary table.

In the past, developers were mostly relegated to one of the following scenarios...

Option 1

• Create a temporary table in your database.
• Call the stored procedure which can use this table just like any other table.

But, you must delete this table after you are done, otherwise next execution may result in an error (if you are creating table without checking whether it already exists).

Option 2

• Create a temporary table with # prefix 
• Call the stored procedure which can use this table just like any other table.

Table created with # or ## prefix is only available for that session. As long as you did everything within the same connection, you are good. Once you close the connection, the table will be automatically deleted.

This works, however creating temporary tables is generally not recommended because every time a temporary table is created, stored procedure must be recompiled and query execution plan must be generated. Moreover, if your stored procedure is used in a multi-user environment, a copy of temporary table will be created for every user - (you can avoid this by creating a global temp table (with ## prefix), but then you must account for the fact that several users may share the same data which may not be desirable). 

Table variables introduced in SQL 2005 solve this problem to a great extent although there may be some performance issues because you can't create non-clustered indexes on table variables. Further, you can't use table variables created in one stored procedure in another because they will be out of scope.

Creating Temp Table

CREATE TABLE #tmpTable(ID INT, Name varchar(100))

Creating Table Variable

DECLARE @tmpTable TABLE(ID INT, Name varchar(100))

As I mentioned earlier, you can't create a table variable in one stored procedure and use it in another. SQL Server 2008 introduced a Table variable type that you can use to pass a table variable from one stored procedure to another.

Create a Table Data Type

/****** Object:  UserDefinedTableType [dbo].[TableType]    Script Date: 05/17/2012 23:07:07 ******/
CREATE TYPE [dbo].[TableType] AS TABLE(
    [CustomerID] [int] NOT NULL,
    [Name] [varchar](255) NULL,
    [City] [varchar](50) NULL,
    [State] [char](2) NULL,
    [Zipcode] [varchar](20) NULL
)
GO

Create a Stored Procedure that declares a variable of this type - Here I am declaring @Customers variable and populating it with data.

ALTER PROCEDURE sp_GetCustomers

AS
BEGIN
    DECLARE @Customers AS TableType
    INSERT INTO @Customers(CustomerID,Name,City,State,ZipCode)
    SELECT C.CustomerID,C.FirstName + ' ' + C.LastName,A.City,A.State,A.ZipCode
    FROM Customers C INNER JOIN CustomerAddress A ON C.CustomerID=A.CustomerID
    EXEC sp_GetCustomersWithAddresses @Customers
    
END

Pass this variable to another Stored Procedure - You must pass a table type variable as READONLY

CREATE PROCEDURE sp_GetCustomersWithAddresses
@Customers TableType READONLY
AS
BEGIN
    SELECT * FROM @Customers
END

You can use table type variable in your applications as well and pass it around like a regular SQL data type.

Thank you.