May 19, 2010 at 12:08 pm
Let me thank you guys for your responses and better clarify by issue:
-- Thanks Steve
I have a Proc that returns one of two (will be more in the future) possible different row sets. The returned columns and criteria use in the selection vary depending on input parameters. The reason for selecting them into a temp table is so I can manipulate and resort the data into special groupings not inherit in the data. Then I return the row set from my temp table with sorting on that manipulated data.
I need to use the same temp table name for each possible SELECT INTO scenario so I can use the same following manipulation process regardless of the scenario the data arrived from.
-- Thanks Atif
The "results" of using the EXECUTE statement are out of scope, the temp table within the EXEC statement doesn't exist after the execution is complete. I need the temp table to do the followup processing. I have not figured out a way to return an 'undefined' table as an OUTPUT variable of an EXEC, to the originating scope (session, batch or what ever it should be called).
-- Thanks William
Dropping an existing Table is not the issue because it doesn't exist, only the runtime "thinks" it does because it is "mentioned" in an previous unexecuted portion of the script.
-- Thanks Mike01--
Adding the precess logic to the sql string to be executed would be more difficult than duplication it after every possible possible source data.
May 20, 2010 at 12:30 am
Another Option;
Create Table #tempA
(
AID int,
ACol1 varchar(100),
ACol2 varchar(100)
)
Create Table #tempB
(
BID int,
BCol1 varchar(100),
BCol2 varchar(100),
BCol3 varchar(100)
)
IF 1 <> 1
BEGIN
Insert into #tempA
Select ID,Code,[Name] From dbo.countries
Select * from #tempA
--And Further Logic of on #tempA
END
ELSE
BEGIN
Insert into #tempB
Select ID,Code,[Name],Region From dbo.countries
Select * from #tempB
--And Further Logic of on #tempB
END
Drop table #tempA
Drop table #tempB
May 20, 2010 at 12:40 am
I dont think that the Logic of both results will be same. What I mean is that the logic for Condition 1=1 would be different from the logic of 1=0 (as you are creating temp table from different sources.)
May 22, 2010 at 8:33 pm
The reason your first method did not work is that the optimizer does not parse conditional when it generates a query plan. So it tries to generate a plan for every statement in the procedure without trying to determine if it would need to execute all of the statements. Therefore, it sees it as if you are trying to create two temp tables with the same name and returns an error.
May 22, 2010 at 9:54 pm
Hmmm. After the EXEC the temp table goes out of scope.
OK, so the solution is...don't use a temp table:
IF OBJECT_ID('myTemp1') IS NOT NULL
DROP TABLE myTemp1
IF 1 <> 0
BEGIN
EXEC sp_executesql N'Select * INTO myTemp1 From TableA'
END
ELSE
BEGIN
EXEC sp_executesql N'Select * INTO myTemp1 From TableB'
END
SELECT * FROM myTemp1
May 23, 2010 at 10:24 am
If you use this option, be sure to explicitly drop the table when done with it. However, this option has the problem that the option can only be used one at a time. If two instances of the procedure try to run, it won't work because the table will already exist.
The better option is to use multiple procedures. This is also better for performance.
Create Procedure dbo.ChildProc1
As
Select *
Into #myTemp1
From sys.tables
Select *
From #myTemp1
Go
Create Procedure dbo.ChildProc2
As
Select *
Into #myTemp1
From sys.procedures
Select *
From #myTemp1
Go
Create Procedure dbo.ParentProc
@InputValue bit
As
IF @InputValue = 0
BEGIN
Exec dbo.ChildProc1
End
Else
Begin
Exec dbo.ChildProc2
End
Go
Exec dbo.ParentProc 0
Go
Exec dbo.ParentProc 1
Go
May 25, 2010 at 7:18 am
-- parent session
IF 1 <> 0
BEGIN
-- child session 1
exec ('Select 1 as col INTO #myTemp1 ')
END
ELSE
BEGIN
-- child session 2
exec ('Select 2 as col INTO #myTemp1 ')
END
-- child session 3
exec ('select * from #myTemp1')
if you create temporary table in the parent seesion, it can be seen and avilible to child session 1 ,2 and 3. Temporary table created in child session 1 can't be seen at parent session , neither same level child session.
May 25, 2010 at 12:26 pm
Not a "good" option, but an option:
Try global ##temp tables with the entire procedure name and @@SPID placed in the table name... if you get really paranoid, an a 6+ digit randomly generated number to the name as well, just to reduce the chances of a collision more.
Note that you need to ensure that the naming convention is always the same, so you don't have some other stored procedure with the same name running.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply