Nested SQL with condition logic

I'm trying to combine two posts I found into one solution but I think I'm going for the impossible.

Here's a shell of the nested select and I've indicated where I'd insert the nested logic.

SELECT node_id, fk_id, nd_src_tbl_name, ( ** conditional code ** )
FROM vNodeCollection as vNc

Here's the nested logic I want to insert for each row:

BEGIN
IF [vNc].nd_src_tbl_name = 'tbl_CorporateRegion'
BEGIN
SELECT cr_textFROM tbl_CorporateRegion WHERE cr_id = [vNc].fk_id
END
ELSE IF [vNc].nd_src_tbl_name = 'tblCountry'
BEGIN
SELECT country_text FROM tbl_Country WHERE country_id = [vNc].fk_id
END
END

I'm trying to be dynamic by storing my table names with my node detail information but I'm starting to realize this may not work well.

Any ideas?No, this will not work. If u want conditional statements in a select, use the CASE functionality. See Books Online for details on how to use it. Alternatively, a user defined function may work as well (if u have SQL Server 2000).|||I forgot about custom functions. After some stumbling with the syntax I was able to make it work. I have no idea if it's efficent though.

Thanks for the idea.

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 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?

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 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?

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?