CODEWITHSUNDEEP
selectstored-proceduressql-server-2014
Reema
Reema

Reputation: 647

Select value from stored procedure which outputs a table

I have a procedure which returns a select statement output after a set of calculation. The output on executing the procedure will be as below:

exec proc1 empID

Output is as below

col1 col2 col3 col4 col5
2014 2 33 330 29
2014 3 10 34 12
2015 1 25 60 55

Now I have a main select statement which gets many columns by joining different tables.

I need to retrieve the above columns (output of stored procedure proc1) to the main select statement, for the empID which is available.

Something like below:

select empID, empName, empSal, (select col3 from [exec proc1 empID] where col2=1), empDept from tblemployee

Is it possible to do? I am expecting 25 in the 4th column of the above query.

Upvotes: 0

Views: 1303

Answers (1)

Shekhar Pankaj
Shekhar Pankaj

Reputation: 9145

You can use either User-defined function or a view instead of a stored procedure.

sp doesn't allow select to use in it.

or you can

  • create a table variable to store the result returned from the stored procedure

Declare @TempTable Table (...)--declare all columns

  • insert the output of the stored proc into the table variable and then

Insert @TempTable Exec storedProcname params

  • Join the Temp table variable exactly as per your need

Note

But the above method has a limitation The problem with INSERT @Temptable is that an

INSERT EXEC statement cannot be nested

. it will break,if your stored procedure already has an INSERT EXEC in it.

Upvotes: 1

Related Questions