need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
...
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns> )
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if
i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:

>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems t
o
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?