If statement not working properly in sql server query

Question

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

possible duplicate of SQL IF block code causing error even though it shouldn't execute — Beth Lang
– Beth Lang, Commented Aug 6, 2015 at 1:11

Felix Pamittan · Accepted Answer · 2015-02-05 05:59:29Z

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

Collectives™ on Stack Overflow

If statement not working properly in sql server query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related