CODEWITHSUNDEEP
sqlsql-server
Saurabh Palatkar
Saurabh Palatkar

Reputation: 3384

If statement not working properly in sql server query

I have a table where I want to run following query

IF COL_LENGTH('table_name','IsDeleted') IS NOT NULL
BEGIN
    IF COL_LENGTH('table_name','IsActive') IS NOT NULL
    BEGIN
        UPDATE table_name 
        SET IsActive = ~IsDeleted
    END
    ELSE
    BEGIN
        EXEC sp_RENAME 'table_name.IsDeleted', 'IsActive', 'COLUMN'

        UPDATE table_name
        SET IsActive = ~IsActive
    END

    ALTER TABLE table_name
    DROP CONSTRAINT DF_table_name_IsDeleted
    ALTER TABLE table_name DROP COLUMN IsDeleted
END
ELSE
BEGIN
     --IsDeleted column does not exist
END

In my table there is no column with name "IsDeleted", so it shouldn't enter in the first if statement. But somehow it enters the first if statement in gives me error message:

Invalid column name 'IsDeleted'."

at line SET IsActive = ~IsDeleted

What is wrong in my query? Why control is not going in else part. Is there any syntax error?

IsActive and IsDeleted are of type BIT

Upvotes: 1

Views: 2822

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

This is because SQL Server parses and validates the statement regardless of the IF statements, meaning it parses and validates the whole batch. So when parsing the statement:

UPDATE table_name SET  IsActive = ~IsDeleted

it errors out saying:

Invalid column name IsDeleted

See this example:

IF 1 = 1 BEGIN
    CREATE TABLE #temp(col INT)
END
ELSE BEGIN
    CREATE TABLE #temp(col INT)
END

It produces an error:

There is already an object named '#temp' in the database.

The workaround is to use Dynamic SQL.

DECLARE @sql NVARCHAR(MAX)
IF COL_LENGTH('table_name','IsDeleted') IS NOT NULL BEGIN

    IF COL_LENGTH('table_name','IsActive') IS NOT NULL BEGIN
        SET @sql = 'UPDATE table_name SET IsActive = ~IsDeleted'
        EXEC sp_executesql @sql
    END
    ELSE BEGIN
        EXEC sp_RENAME 'table_name.IsDeleted', 'IsActive', 'COLUMN'
        SET @sql = 'UPDATE table_name SET IsActive = ~IsActive'     
        EXEC sp_executesql @sql
    END
    ....
END

Upvotes: 3

Related Questions