CODEWITHSUNDEEP
sqlsql-servert-sql
Yush N
Yush N

Reputation: 85

How to use SQL column name only if the column exists?

I've been trying to get a textual result from CASE(def.OPTION_CATEGORY_ID) in a select, but I'm having a hard time implementing the terms.

What I'm trying to do is to check if OPTION_CATEGORY_ID is an existing column in sys.columns. If it is then I'm trying to make the int to text translation using the When - Then on the bottom but the SQL is not aware of the column name so CASE(def.OPTION_CATEGORY_ID) is failing because the column name is invalid.

Is there any way to call def.OPTION_CATEGORY_ID using an alias name so the SQL won't fail it before hand? Thanks

SELECT DISTINCT *
    FROM
      (SELECT def.OPTION_DEF_ID AS 'Def ID',
              ass.ASSET_NAME AS 'Asset Name',
              CASE(def.OPTION_TYPE_ID)
                  WHEN 1 THEN 'A'
                  WHEN 2 THEN 'B'

              END AS 'Option Type',
              case when exists (SELECT name
              FROM   sys.columns 
              WHERE  Name  = 'OPTION_CATEGORY_ID'
                     AND Object_ID = Object_ID(N'TFC_OPTION_DEFINITION')) 

    then
        -- Column Exists
         CASE(def.OPTION_CATEGORY_ID) 
                  WHEN 1 THEN 'C'
                  WHEN 2 THEN 'D'
                  WHEN 3 THEN 'E'
                  WHEN 4 THEN 'F'


              end
              End AS 'test' ,

        -- the rest of the select

Upvotes: 1

Views: 150

Answers (1)

Yosi Dahari
Yosi Dahari

Reputation: 6999

If you want to select a column and you are not sure if it exists, you cannot write it explicitly in your code, and expect it to compile.

You should build the statement dynamically based on the run time information of which the column exists or not:

DECLARE @statement VARCHAR(MAX)

IF((SELECT COUNT(*)
              FROM   sys.columns 
              WHERE  Name  = 'OPTION_CATEGORY_ID'
                     AND Object_ID = Object_ID(N'TFC_OPTION_DEFINITION')) > 0)
BEGIN
 SET @statement = --assign a query which uses OPTION_CATEGORY_ID
END
ELSE
BEGIN
 SET @statement = --assign a query which does not use OPTION_CATEGORY_ID
END

EXEC sp_executesql @statement

Upvotes: 2

Related Questions