Insert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results )
-
Dear All, I would like to show you the following questions ( SQL server ): - I have one procedure that return 2 result after execute Eg: create procedure spTest as begin select * from table1 select * from table2 end ( this is just sample procedure that return 2 result after execute ) - I also have one temp Table name #tmpTable1, i want to insert into this temp table, but get result from procedure above, but i can't use this statement: insert into #tmpTable1 exec spTest ( it will not work, because this procedure return 2 result ) Do you have any idea to get result from this procedure into my temp table? Thank you in advance for your good idea...
Mr. LTM KH
-
Dear All, I would like to show you the following questions ( SQL server ): - I have one procedure that return 2 result after execute Eg: create procedure spTest as begin select * from table1 select * from table2 end ( this is just sample procedure that return 2 result after execute ) - I also have one temp Table name #tmpTable1, i want to insert into this temp table, but get result from procedure above, but i can't use this statement: insert into #tmpTable1 exec spTest ( it will not work, because this procedure return 2 result ) Do you have any idea to get result from this procedure into my temp table? Thank you in advance for your good idea...
Mr. LTM KH
You have a some of choices, the obvious one is to split the child procedure into 2 procs, this is by far the better choice. You could also use a global temp table in the child procs declare as
##TableName. IE create the global temp in the parent proc and call the insert proc, then consume the results in the parent proc. I would consider this a really crappy solution as it is fraught with problems. You could also modify the child proc to use aUNIONand return only 1 table (assumes if you are going to insert the data into 1 table then it is the same structure) MS did a great disservice when they allowed multiple result sets to be returned from a single proc.Never underestimate the power of human stupidity RAH
-
You have a some of choices, the obvious one is to split the child procedure into 2 procs, this is by far the better choice. You could also use a global temp table in the child procs declare as
##TableName. IE create the global temp in the parent proc and call the insert proc, then consume the results in the parent proc. I would consider this a really crappy solution as it is fraught with problems. You could also modify the child proc to use aUNIONand return only 1 table (assumes if you are going to insert the data into 1 table then it is the same structure) MS did a great disservice when they allowed multiple result sets to be returned from a single proc.Never underestimate the power of human stupidity RAH
-
Dear Mycroft Holmes, Thanks you so much for your solutions, I will test it with global temp table... But this may not the best choice for me.. Regard
Mr.LTM-KH
LTM_it_kh wrote:
But this may not the best choice for me.
It may well be the worst solution! Global temp table are nasty to manage!
Never underestimate the power of human stupidity RAH