Solved

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

Posted on 2008-10-14
3
206 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
ID: 22716960
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]
ID: 22716961
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
ID: 31506135
Thanks much.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

16 Experts available now in Live!

Get 1:1 Help Now