Return result of SELECT to another procedure

Question

I have problem with "returning" one procedure result to another procedure.

Description:

For example we have complex SELECT with parameters putted in one procedure.

USE [myDB]
GO

ALTER PROCEDURE dbo.Procedure1 @today date
AS


SELECT * FROM complex_query

now it's returning a lot of columns and numbers of columns and rows depends on @today value.

No I want to use result of Procedure1 in Procedure2

USE [myDB]
GO

DECLARE @cntMonth INT = 0;

WHILE @cntMonth < 12  
BEGIN
  SELECT x,y,z FROM result_of_Procedure1
  SET @cntMonth = @cntMonth + 1;
END;

why I want it like that?
It's because the software I'm working on is connecting to DB, run procedure and get the result to frontend. So I have to code all the way in SQL.
So assuming software is doing one-time connection I don't want to got through Procedure1 every time WHILE statement is executed. How can I store the effect of Procedure1 to use it through Procedure2 while loop and prevent multiple runs of Procedure1?

score 3 · Accepted Answer · answered May 06 '16 at 14:09

3

CREATE TABLE #TempResults
( 
  -- put definition of columns returned from your Procedure1 SP here
)

INSERT INTO #TempResults
EXEC Procedure1

-- now you have your result from Procedure1 in #TempResults
-- use it whatever way you want
SELECT * FROM #TempResults

-- finally drop the temp table when no longer needed
DROP TABLE #TempResults

answered May 06 '16 at 14:09

Pradeep Kumar

6,836
4
21
47

ok, and if second user will start this procedure in same time will there be a problem with temp table #TempResults already existing? – susanoo May 06 '16 at 14:20
No, each temp table (created with `#` prefix) is unique to a session/connection. So two users won't mess up with each other. – Pradeep Kumar May 06 '16 at 14:21

Return result of SELECT to another procedure

1 Answers1