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 |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2010-09-24 : 09:18:52
|
| I created a few store procedures, s1, s2, s3, s4.In s1, I use a temporary table to store a data like below:select * into #order from order where city='NY'select * from #orderNow, the another store procedure, s2,s3,s4, will use the result "select * from #order" to do further process. I knew that temporary table will destroy after the store procedure close.Is there another way to handle it? |
|
|
RajJol
Starting Member
17 Posts |
Posted - 2010-09-24 : 09:55:30
|
| Do not use temp tables. Create tables as per normal and DROP them once you have finished with your stores. |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-24 : 10:04:35
|
| Don't do that.Don't create (and then drop) persistence objects for passing information back and forward between stored procedure - It leads to messy messy problems in concurrent systems.I think you may want to read this:http://www.sommarskog.se/share_data.htmlWhich is an *excellent* article on the subject of sharing data between procedures.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
rohitvishwakarma
Posting Yak Master
232 Posts |
Posted - 2010-09-24 : 10:36:30
|
You may use a Global temp table using ##temp which can be accessed in other procedures.quote:
Originally posted by Sun Foster
I created a few store procedures, s1, s2, s3, s4.In s1, I use a temporary table to store a data like below:select * into #order from order where city='NY'select * from #orderNow, the another store procedure, s2,s3,s4, will use the result "select * from #order" to do further process. I knew that temporary table will destroy after the store procedure close.Is there another way to handle it?
|
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-24 : 11:06:13
|
| That's also not such a hot idea. If you want to run multiple concurrent copies of the stored procs involved you are going to be in trouble with a global temp table.I advise you to read the article I posted.you can reference the same temp table (not a global one) if you are willing to nest the procedures.or you could use a process keyed table..... which I don't like so much.Read the article.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Using temporary table