We have noticed that when we use a temp table in a stored procedure and then execute the procedure, the temp table remains. So if we execute the procedure again, it will fail with a table already exists error.
Are we missing something? Here is what we are trying to do:
CREATE PROCEDURE SessionVariableGetMany
SessionId CHAR ( 16 ),
VarNames CHAR ( 2000 ),
CURSOR VARYING OUTPUT
Declare @Sql char(2000);
@Sql = 'Select SV_Key, SV_Value Into #tempSVGM From SessionV Where SV_CustNum = ''' + _SessionID + ''' And SV_Key In (' + RTrim(_VarNames) + ');';
Execute Immediate RTrim(@Sql);
Select * From #tempSVGM;
Any insight into this would be greatly appreciated.