?
Solved

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

Posted on 2008-10-14
3
Medium Priority
?
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 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 143

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 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