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 |
|
engcanada
Starting Member
39 Posts |
 Posted - 2011-06-04 : 22:14:34
|
| Hi,When I create a single stored proc. using the follwing and call it then I have no problem but when I seperate it into 3 different stored proc; CREATE TABLE, INSERT INTO and then SELECT I get an error on the INSERT INTO statement as: Invalid object name '#MyTempTable'. CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)INSERT INTO #MyTempTable VALUES (1)SELECT * FROM #MyTempTableAny idea?Thanks |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-04 : 22:54:14
|
| This is because a local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. There is a description on the scope and rules on this page which may help: http://msdn.microsoft.com/en-us/library/aa258255(v=sql.80).aspx |
 |
|
|
engcanada
Starting Member
39 Posts |
Posted - 2011-06-04 : 23:09:45
|
| Is there a way to work around this issue?My "Create temp Table" statement is outside and the "Insert Into" statement is inside a loop and I cant't run the entire proc. outside. |
|
|
|
engcanada
Starting Member
39 Posts |
Posted - 2011-06-04 : 23:25:03
|
| I think I found an alternative. I am just going to create a regular table, retreive the desired data from it and then drop it right away. It worked the same way.Cheers |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-05 : 00:10:38
|
| You can create a regular table or a global temp table (name starting with ##). In both cases it would be ok unless multiple users/processes invoke your code simultaneously. If that is a possibility, either you will need to program to account for that. Alternatively, you could create the temp table in a stored proc, and call the other two stored procs from the first stored proc. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-06-05 : 12:24:20
|
quote:
Is there a way to work around this issue?My "Create temp Table" statement is outside and the "Insert Into" statement is inside a loop and I cant't run the entire proc. outside.
Describe what you are actually trying to do. I'm 95% certain that there will be a nice fast way to do it, probably without needing any kind of temp storage.Insert inside a loop is a code smell. There is almost certainly a better way.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
Temp tables