Do you have a series of stored procedures in SQL Server that need to pass data between themselves? There is more than one way to do this, however, using a temporary table is a good way. Suppose you have one main stored procedure that calls several other stored procedures. One of your stored procedure computes a value and needs to pass it to another stored procedure that follows it in the series of stored procedures that the top, main stored procedure calls.

Suppose you have a top main stored procedure called spAll. spAll calls two stored procedures called sp1 and sp2. sp1 computes a value (or any data) and saves it to the temporary table created in spAll. In this way, sp2 has access to that temporary table. It is like creating global variables. Please excuse the poor table names as they are meant to make the code easier to read when you are first learning how it works.

Below is some code that will prove that it works.

CREATE PROC [dbo].[spAll]
AS
BEGIN
	CREATE TABLE #tmptbl_All (mySavedInt INT);
	INSERT INTO #tmptbl_All (mySavedInt) VALUES (0);
	PRINT 'Now in spAll; selecting mySavedInt'
	SELECT mySavedInt FROM #tmptbl_All;
	PRINT 'Still in spAll; calling sp1 and then sp2...'
	EXEC sp1
	EXEC sp2
END 

CREATE PROC [dbo].[sp1]
AS
	PRINT 'In SProc dbo.sp1; updating column mySavedInt to 68...'
	UPDATE #tmptbl_All SET mySavedInt = 68;
GO

CREATE PROC [dbo].[sp2]
AS
	PRINT 'Now in sproc sp2, seleting mySavedInt from #tmptbl_All...'
	SELECT mySavedInt FROM #tmptbl_All;
GO

If we execute the main procedure apAll, the procedure sp2 should be able to retrieve the value 68 that the procedure sp1 saved in the temporary table. Below is the output in the Results pane of SSMS when we run the main stored procedure spAll.

(1 row(s) affected)
Now in spAll; selecting mySavedInt
mySavedInt
-----------
0

(1 row(s) affected)

Still in spAll; calling sp1 and then sp2...
In SProc dbo.sp1; updating column mySavedInt to 68...

(1 row(s) affected)
Now in sproc sp2, seleting mySavedInt from #tmptbl_All...
mySavedInt
-----------
68

(1 row(s) affected)

Return Value
------------
0

(1 row(s) affected)