Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pdubey
Starting Member
1 Post |
 Posted - 2008-12-18 : 06:11:08
|
| Hi all,I have a query regarding the nested SP's and use of Global Temp Table in it.The thing is I am calling SP2(lets say) from Sp1. This Sp2 is fetching multiple records from a table and these records needs to be sent back to SP1. Now the problem here is can I define a GTT in SP1 and insert data in it in SP2 and again retrieve it in SP1.If so can someone tell me how and what will be the syntax..This is really a showstopper for me?Immediate response is higly appreciated. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-12-18 : 09:53:23
|
| You don't need a global temp table for this (ie: ##temp). If you have concurrent users running SP1 then they will collide as they would with a permanent table.If SP1 creates a regular temp table (ie: #temp) then that table is in scope for any SPs that SP1 calls. So SP2 can populate #temp even though it was created in SP1. SP2 then would be dependent on SP1 being its caller.EDIT:depending on your objective you can also simply SELECT the rows as output from SP2. Then within SP1 populate a table with the results of the EXEC SP2.ie:insert #t (<same columns returned from SP2>)exec SP2Be One with the OptimizerTG |
 |
|
|
|
|
|
How to build nested SP's using Global Temp Table