MS SQL : Using Temp table between Stored Procedures

Suppose we want the Temporary tables to be shared across stored procedures, it can be achieved in the following way -

Create an SP which is called by other SP:
CREATE PROC InsertRecords
AS
BEGIN
INSERT INTO #GlobalTemp values(1,’Akash’)
INSERT INTO #GlobalTemp values(2,’Arun’)
INSERT INTO #GlobalTemp values(3,’Chitra’)
END

Create an SP which calls the above SP:
CREATE PROC CallingSP
AS
BEGIN
CREATE TABLE #GlobalTemp(EmpId INT, EmpName VARCHAR(20))
EXEC InsertRecords
SELECT * FROM #GlobalTemp
END

In the first SP, we are just inserting the values in the temp table ‘#GlobalTemp’, but it is not declared there, instead. The ‘#GlobalTemp’ table is declared in the SP ‘CallingSP’ and is used by the SP ‘InsertRecords’.

Now if you execute -
EXEC CallingSP

you will get the output as follows -
1 Akash
2 Arun
3 Chitra