Select value from stored procedure which outputs a table -

i have procedure returns select statement output after set of calculation. output on executing procedure below:

exec proc1 empid

output below

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

now have main select statement gets many columns joining different tables.

i need retrieve above columns (output of stored procedure proc1) main select statement, empid available.

something below:

select empid, empname, empsal, (select col3 [exec proc1 empid] col2=1), empdept tblemployee

is possible do? expecting 25 in 4th column of above query.

you can use either user-defined function or view instead of stored procedure.

sp doesn't allow select use in it.

or can

• create table variable store result returned stored procedure

declare @temptable table (...)--declare columns

• insert output of stored proc table variable , then

insert @temptable exec storedprocname params

• join temp table variable per need

note

but above method has limitation problem insert @temptable

insert exec statement cannot nested

. break,if stored procedure has insert exec in it.