Temporary tables within an IF statement

Question

Temporary tables within an IF statement

Mark Harley

SSCrazy

Points: 2259
More actions
January 15, 2008 at 7:35 am

#185534

I have a situation in which I need to fill a temporary table in different ways, depending on the variables passed in to the stored procedure. The problem I am facing is that when I try to use the same temp table name in each of the if-possibilities, I get the following error:
"There is already an object named '#thisPeriod' in the database."
Can I not use the same table name in mutually exclusive circumstances? The table is referenced in other parts of the sp, so I don't want to have to determine which table to reference in multiple areas.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

This is similar to variables (these are pulled out of the script before execution, so a particular declaration is evaluated only once). Not sure how nice it is to use the same temptable name for different schemata though. It could be confusing. However if you want to use the same name, you may consider to use a merged schema? Assuming the column names that would be shared are not using different data types. Select statements should never use * in production code anyway, and if the columns are variable, they will not even introduce much space overhead.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Mark Harley SSCrazy Points: 2259 More actions · Answer 2

Is there an easier way to fill a table from different sources, depending on the value of a variable?

GSquared SSC Guru Points: 260824 More actions · Answer 3

The only way I know to do this is to define the temp table before the IF statement. Here's an example that might work for you:

create table #T1 (ID int identity primary key)

if 1 = 2

begin

alter table #t1 add C1 int

insert into #t1 (c1) select 1

end

else

begin

alter table #t1 add C1 char(1)

insert into #t1 (c1) select 'a'

end

select * from #t1

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 4

Of course, my solution only works if the table definition is different depending on what the variables do in the IF statements.

If you have a single table definition, just define the table once, before the IF, and then use the IF statement only for the INSERT commands.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Are you continually testing this thing using Query analyzer or SSMS? If you are - you might simply be getting that because the temp table isn't being cleaned up from the last time you ran the procedure.

If your connection doesn't get dropped - the temp table doesn't get dropped. Have you tried simply dropping the temp table at the beginning of the process?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

If your scope is only the local proc, try to use table variables

declare @tmptb table (colx ... )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Mark Harley SSCrazy Points: 2259 More actions · Answer 7

The definition of the table wasn't changing, so defining the table before the IF statement worked perfectly. I am testing this in the Query Analyzer, but I was also dropping the temp tables each time I ran it.

Thanks for all the help.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

Unless you have strict rules for temptb's I wouldn't rely on "they have the same definition" :ermm:

You can off course always test the existance using

select OBJECT_ID('tempdb..#mytmpTb')