0

I am facing the situation where I need to use result of one stored procedure within another stored procedure. My first stored procedure returns a subset of a table and I want to use it in second stored procedure.

The exec dbo.myprcedure param returns the result but I need to capture it within another stored procedure.

Any help would be appreciated.

Shiva
  • 20,575
  • 14
  • 82
  • 112
Lali
  • 2,816
  • 4
  • 30
  • 47

4 Answers4

0

Instead of stored procedure you can use view..A view can contain selected data's from a table.First create one view then call view in your stored procedure

or

create a temp table.from temp table you can get data's

Nagaraj S
  • 13,316
  • 6
  • 32
  • 53
  • I am using Entity Framework 6 and I don't want to use Views because they make it too slow to dig out data. – Lali Feb 10 '14 at 10:07
0

Provided it returns just one result set, you can create a temp table then use syntax 

INSERT INTO #tmp
EXEC dbo.myprocedure @param

see https://stackoverflow.com/a/14623262/8479

But in general it's better to find a different approach, e.g. use table-valued variables or use a function instead of a proc.

Community
  • 1
  • 1
Rory
  • 40,559
  • 52
  • 175
  • 261
  • I could not understand your better approach in last line. your answer is impressive. – Lali Feb 10 '14 at 10:11
0

Execute the 1st stored Procedure inside you Secod stored Procedure and use the results there. Something like 

CREATE PROCEDURE Proc_2
@param
AS
BEGIN
 SET NOCOUNT ON;

/* Create a Temp Table here say #TempTable*/

INSERT INTO #TempTable
exec dbo.myporcedure @param 

/* Your rest of the code that needs to work with the result set of dbo.myporcedure */

END
M.Ali
  • 67,945
  • 13
  • 101
  • 127
0

Just return a TABLE VARIABLE in either a FUNCTION or a STORED PROCEDURE and then use that TABLE VARIABLE in your caller Stored Procedure for further processing or to pass on to another Stored Procedure as a TABLE VARIABLE Parameter.

i.e. Create a Function as follows that returns a TABLE VARIABLE.

CREATE PROCEDURE MyProcedureThatReturnsATable()
RETURNS  @ReturnTable TABLE 
(
    -- specify columns returned by the proc here
    ID INT NOT NULL,
    Name nvarchar(255) NOT NULL
)
AS
BEGIN
.
..
...

--This select returns data 
INSERT INTO @ReturnTable
SELECT ID, Name FROM SOME_TABLES

RETURN

END

Then in the parent Stored Procedure, you would execute above stored procedure and populate a TABLE Variable in that parent Stored Procedure as follows.

DECLARE @MyParentTableVariable as TABLE (ID INT, Name nvarchar(255))

INSERT INTO @MyParentTableVariable
EXECUTE MyProcedureThatReturnsATable

So now, in the parent Stored Procedure, you have the data from the MyProcedureThatReturnsATable in a TABLE VARIABLE.

Shiva
  • 20,575
  • 14
  • 82
  • 112