passing temp tables between stored procedures

Its not widely known that you can pass temp tables between stored procedures.

The basic idea is that you create a parent procedure which contains the temp table, and one or more child procedures that reference the table. The parent procedure will then call the child procedures after the temp table has been created.

A basic example can be seen below:

USE test
GO

CREATE PROC dbo.pass_temp_table_between_stored_procs

AS

SELECT GETDATE() x INTO #temp

EXEC test.dbo.pass_temp_table_between_stored_procs1
EXEC test.dbo.pass_temp_table_between_stored_procs2

GO

CREATE PROC dbo.pass_temp_table_between_stored_procs1
AS
SELECT * FROM #temp

GO

alter PROC dbo.pass_temp_table_between_stored_procs2
AS

UPDATE #temp SET x = x+1

SELECT * FROM #temp

GO

EXEC dbo.pass_temp_table_between_stored_procs

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: