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 |
|
lane0618
Posting Yak Master
134 Posts |
 Posted - 2002-07-09 : 12:22:57
|
| The following is part of a sp that inserts into a temp table:declare @sql varchar(1000)select @sql = 'select item into #tempFROM specsub.dbo.[ANA No Dups]where (item like ' select @sql = @sql + @quote + @searchstring1 + @quote +' or item like ' + @quote + @searchstring2 + @quote +'or item like ' + @quote + @searchstring3 + @quote +'or item like ' + @quote + @searchstring4 + @quote +'or item like ' + @quote + @searchstring5 + @quote +'or item like ' + @quote + @searchstring6 + @quote +'or item like ' + @quote + @searchstring7 + @quote +'or item like ' + @quote + @searchstring8 + @quote +'or item like ' + @quote + @searchstring9 + @quote +'or item like ' + @quote + @searchstring10 + @quote + ')' + ' and status like ' + @quote + 'preact' + @quoteexec (@sql)select * from #tempWhen I run it I get this error:Invalid object name '#temp'.Why isn't the #temp table created per the dynamic sql?Thanks,Lane |
|
|
macka
Posting Yak Master
162 Posts |
Posted - 2002-07-09 : 12:29:43
|
| The temp table is not in scope when you are trying to access it. Try creating the table manually before you call the dynamic SQL with:CREATE TABLE #tmp(pk INTEGER,somefield VARCHAR(50) .... ) Take a look at the scope section of this article:[url]http://www.sqlteam.com/item.asp?ItemID=4619[/url]macka.Edited by - macka on 07/09/2002 12:34:49 |
 |
|
|
dsdeming
479 Posts |
Posted - 2002-07-09 : 12:41:43
|
| Try using a global temp table ( ##temp ). Such tables don't have the scope limitations of ordinary temp tables. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-09 : 13:09:59
|
quote:
Try using a global temp table ( ##temp ). Such tables don't have the scope limitations of ordinary temp tables.
The global temp table wouldn't work in this example. Global temp tables are accessible across spids, yes, but they are garbage collected when their reference count equals zero, just like a local temp table. So, when the exec() call returns, the table will still be destroyed. The earlier suggestion about creating a local temp table in the calling scope will work.Jonathan Boott, MCDBA |
|
|
|
lane0618
Posting Yak Master
134 Posts |
Posted - 2002-07-09 : 13:24:37
|
That does the trick.Thanks!Lanequote:
The temp table is not in scope when you are trying to access it. Try creating the table manually before you call the dynamic SQL with:CREATE TABLE #tmp(pk INTEGER,somefield VARCHAR(50) .... ) Take a look at the scope section of this article:[url]http://www.sqlteam.com/item.asp?ItemID=4619[/url]macka.Edited by - macka on 07/09/2002 12:34:49
|
|
|
|
|
|
|
#temp table