Solved

How do I select a value from a column only if the column exists?

Posted on 2008-10-14
3
194 Views
Last Modified: 2010-04-21
I need to develop a DDL that retrieves a value from a column of a table.  However, when the DDL is executed on some databases, the column may or may not exist.  My below code is not working:

DECLARE @TSN VARCHAR(50)  --to store theater server url from THEATER_LK
SET @TSN = NULL
IF EXISTS (SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'THEATER_LK' AND COLUMN_NAME = N'DTHEATER_SERVER_NAME')
        SELECT @TSN=DTHEATER_SERVER_NAME FROM THEATER_LK

This returns the following error when the column does not exist.  What's wrong with my syntax?
"Invalid column name 'DTHEATER_SERVER_NAME'

Thanks.
0
Comment
Question by:spazjr01
3 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
Comment Utility
The SQL is parsed at run time and it sees what you are trying to do and errors out.  Try this.

Also, no need to set @TSN=null because an uninitialized variable is NULL.

Try this approach:


DECLARE @TSN VARCHAR(50)  --to store theater server url from THEATER_LK

          ,@SQL nvarchar(max)

          ,@parmList nvarchar(max)

IF EXISTS (SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_NAME = N'THEATER_LK' AND COLUMN_NAME = N'DTHEATER_SERVER_NAME')

begin

     set @SQL = 'SELECT @TSN=DTHEATER_SERVER_NAME FROM THEATER_LK'

     set @parmList = '@TSN varchar(50) output'
 

exec sp_ExecuteSQL @SQL, @parmList, @TSN output

end

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
this is because the whole script is parsed on start, and hence it fails with the column which does not exist.

workaround is dynamic sql:

DECLARE @TSN VARCHAR(50)  --to store theater server url from THEATER_LK 
SET @TSN = NULL 
IF EXISTS (SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = N'THEATER_LK' AND COLUMN_NAME = N'DTHEATER_SERVER_NAME')
BEGIN
   EXEC SP_EXECUTESQL N'SELECT @TSN=DTHEATER_SERVER_NAME FROM THEATER_LK', N'@TSN VARCHAR(50) OUTPUT', @TSN OUTPUT
 END

Open in new window

0
 

Author Closing Comment

by:spazjr01
Comment Utility
Thanks much.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now