AllInWorld99 provides a reference manual covering many aspects of web programming, including technologies such as HTML, XHTML, CSS, XML, JavaScript, PHP, ASP, SQL,FLASH, jQuery, java, for loop, switch case, if, if else, for...of, for...in, for...each,while loop, blogger tips, blogger meta tag generator, blogger tricks, blogger pagination, client side script, html code editor, javascript editor with instant output, css editor, online html editor, materialize css tutorial, materialize css dropdown list,break, continue statement, label,array, json, get day and month dropdown list using c# code, CSS button,protect cd or pendrive from virus, cordova, android example, html and css to make android app, html code play,telerik show hide column, Transparent image convertor, copy to clipboard using javascript without using any swf file, simple animation using css, SQL etc. AllInWorld99 presents thousands of code examples (accompanied with source code) which can be copied/downloaded independantly. By using the online editor provided,readers can edit the examples and execute the code experimentally.


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

Total Pageviews