Get the result set from another store procedure
In some situation we need to join a table with another store procedure result, in this situation you can use the following steps.exec emp_sp_HeadCount_Department 2;
The above line will display following output.
If you want to join this result with another table, for example following table.
select * from Emp_Tb_Tran_Transaction
You need to create a temporary table, for the result set with all field like below
create table #temptbl --Must create a temp table with result set column field ( ID int, ParentID int, ProfileID int, CategoryName varchar(max), Head int );
After created the temporary table, just insert the result set to the temporary table.
INSERT #temptbl exec emp_sp_HeadCount_Department 2;
Now you can join result set(from #temptbl table) and table
select * from #temptbl Tmp join Emp_Tb_Tran_Transaction TR on Tmp.ID=TR.TransactionID
Finally you should drop your temporary table, otherwise have chance to display error shown below.
drop table #temptbl
Final SQL Query is
create table #temptbl --Must create a temp table with result set column field ( ID int, ParentID int, ProfileID int, CategoryName varchar(max), Head int ); INSERT #temptbl exec emp_sp_HeadCount_Department 2; select * from #temptbl Tmp join Emp_Tb_Tran_Transaction TR on Tmp.ID=TR.TransactionID drop table #temptbl
0 comments:
Post a Comment