Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-14
3
Medium Priority
?
244 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

610 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