Updating Fields based on Table Existance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updating Fields based on Table Existance

Author

Topic

John0923
Starting Member

3 Posts

Posted - 2009-06-24 : 17:32:33

I am looking to write a select statement that will conditionally update a field in a select statement based on whether or not a table exists and leaves the field as null if the table does not exist. I am aware that this is possible within stored procedures by using IF statements and objectID() but all of my attempts to use it encased within a single select statement are hitting snags and I'm wondering if it is even possible.

The code I tried writing that is not working is as follows, I realize that the below code is fundamentally flawed but I'm hoping that someone reads and understands the concept.

select item1,
item2,
item3 = CASE
WHEN OBJECTID(dbo.TableTwo,'U') IS NULL THEN NULL
ELSE (select item3 from dbo.TableTwo where a.ID = TableTwo.ID)
END
FROM dbo.TableOne a

Please keep in mind when reading the above example that TableTwo may or may not exist at the time of the run. Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 17:56:18

Try something like this

select item1,
item2,
item3 = CASE
WHEN OBJECT_ID('TableTwo') IS NULL THEN NULL
ELSE (select item3 from TableTwo where a.ID = TableTwo.ID)
END
FROM TableOne a

No, you're never too old to Yak'n'Roll if you're too young to die.

John0923
Starting Member

3 Posts

Posted - 2009-06-25 : 09:55:24

That definitely fixes the case statement! but the else statement still bombs out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 10:07:54

Which error?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 10:08:51

select item1,
item2,
item3 = CASE
WHEN OBJECTID('dbo.TableTwo', 'U') IS NULL THEN NULL
ELSE (select item3 from dbo.TableTwo where a.ID = TableTwo.ID)
END
FROM dbo.TableOne a

E 12°55'05.63"
N 56°04'39.26"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-25 : 11:00:10

quote:
Originally posted by Peso

select item1,
item2,
item3 = CASE
WHEN OBJECTID('dbo.TableTwo', 'U') IS NULL THEN NULL
ELSE (select item3 from dbo.TableTwo where a.ID = TableTwo.ID)
END
FROM dbo.TableOne a

E 12°55'05.63"
N 56°04'39.26"

Oh man - I have done a sloppy work

Thank you for correction Peso.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 13:58:00

Add a "TOP 1" for the subquery in the ELSE clause.

E 12°55'05.63"
N 56°04'39.26"

John0923
Starting Member

3 Posts

Posted - 2009-06-26 : 11:35:44

Thanks for the help everyone. The current scenario and the code as its written gives an error similar to the following (table names changed to be in line with the given scenario):

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.TableTwo.

I believe that even though the WHEN statement is saying to apply nulls if the table doesn't exist, it is still looking to parse the ELSE condition regardless of the results of the WHEN statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 13:00:21

Yes.
It does this at compile time.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources