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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-08 : 08:32:20
|
| Ali writes "i am writing a Stored Procedure in which i have to handle multiple IDs at a time i am taking these IDs as input from the user in form or varchar values now when i execute a dynamic query based on these IDs and take all the data in #Temp1 table it is executed well. but i could not get the data from the #Temp1 table e.gdeclare @sSqlStatement varchar(1000), @strIDs varchar(1000)select @strIDs= '10,12,14,15,19,20'select @sSqlStatement = 'Select * INTO #Temp1 From Employees WHERE EmployeeID IN ('+ @strIDs + ')' Exec(@sSqlStatement)Select * From #Temp1(6 row(s) affected)Server: Msg 208, Level 16, State 1, Line 1Invalid object name '#Temp1'.Can you help me to fetch these 6 row(s) now" |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-08 : 10:25:44
|
| declare @sSqlStatement varchar(1000), @strIDs varchar(1000)select @strIDs= '10,12,14,15,19,20'select @sSqlStatement = 'Select * INTO ##Temp1 From Employees WHERE EmployeeID IN ('+ @strIDs + ')' Exec(@sSqlStatement)Select * From ##Temp1This did worked for me. I'm not sure why your local temp table is not working and why global temp table is working. Some body here should be able shed light on that.Karunakaran |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-08 : 12:02:43
|
| If you want the #temp table to be available after the EXEC, you should use a CREATE TABLE to create #temp before the EXEC, and use an INSERT INTO inside the EXEC. The #temp table created inside the EXEC goes away when the EXEC completes.You should not use the global ##temp table, bacause it is global to the whole server, and some other proc could used the same name and overlay your table or drop it.CODO ERGO SUM |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-09 : 02:10:24
|
| Thanks for the input Michael. I havent worked with Temptables, when I'm working with temp tables I'll keep this in mind.Karunakaran |
|
|
|
|
|
|
|
|
SOS