CODEWITHSUNDEEP
sql-servert-sql
pinegulf
pinegulf

Reputation: 1396

T SQL if column exists then (if it has value X then (

I need to perform the following logical clause:

If column is present and it has certain value then do something. If not, then do something else. 

IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'legacyoptions'
      AND Object_ID = Object_ID(N'config '))
BEGIN
    if ( select legacyoptions from config)=1 
        begin 
            Do stuff when legacy=1
        end
        else begin
            Do stuff when legacy !=1 
        END 
else 
begin
  do stuff when legacy is not present
end

However, this does not work in case legacyoptions is not present

Upvotes: 2

Views: 1286

Answers (3)

valex
valex

Reputation: 24134

Here is the way using TRY CATCH block and a dynamic SQL so this block of code will be compiled without table config and/or legacyoptionsfield in the database. 

BEGIN TRY
    DECLARE @legacyoptions int;  
    EXECUTE sp_executesql N'select TOP 1 @legacyoptions=legacyoptions from config', 
                          N'@legacyoptions int OUTPUT', 
                          @legacyoptions OUTPUT;
    if @legacyoptions=1 
        begin 
          -- Do stuff when legacy=1
        end
        ELSE 
        BEGIN
          -- Do stuff when legacy !=1 
        END   
END TRY  
BEGIN CATCH  
     -- do stuff when legacy is not present
END CATCH

Upvotes: 1

pinegulf
pinegulf

Reputation: 1396

This works, but seems stupid to me.

IF EXISTS( SELECT * FROM sys.columns WHERE Name = N'legacyoptions' AND Object_ID = Object_ID(N'config ')) BEGIN exec(' if (select legacyoptions from config)=1 begin print ''Config==1'' end else begin print ''Config!=1'' end ') end else begin print 'no legacy' end

Upvotes: 0

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

try this :(i guess you are leaving an end of if)

IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'legacyoptions'
      AND Object_ID = Object_ID(N'config '))
BEGIN
    if ( select legacyoptions from config)=1 
        begin 
            Do stuff when legacy=1
        end
        else begin
            Do stuff when legacy !=1 
        END 
end
else 
begin
  do stuff when legacy is not present
end

Upvotes: 1

Related Questions