CODEWITHSUNDEEP
sql-server
Travis Heeter
Travis Heeter

Reputation: 14084

'IF' does not prevent error, but only if it has already executed

I'm trying to move a column from one table to another (here's the post for that),

However this runs as a task, and may run after it has already completed so I need a clause that prevents the logic from running again. I thought I could achieve this with an IF:

IF EXISTS ( 
    SELECT * 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(N'Table_A')
        AND name = 'internalID'
)
BEGIN
    UPDATE Table_B
        SET b.internalID = a.internal_ID
    FROM Table_B b INNER JOIN
            Table_A a
            ON a.id = b.FK_toTableA;

    ALTER TABLE Table_A DROP COLUMN internalID;
END

However, I get an error at 

SET b.internalID = a.internal_ID

The error is:

Invalid column name 'internalID'.

But Only If a.internalID doesn't exist anymore.

Upvotes: 1

Views: 63

Answers (1)

Travis Heeter
Travis Heeter

Reputation: 14084

I had to use EXEC sp_executesql:

IF EXISTS ( 
    SELECT * 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(N'Table_A')
        AND name = 'internalID'
)
BEGIN
    EXEC sp_executesql N'UPDATE Table_B
            SET b.internalID = a.internal_ID
        FROM Table_B b INNER JOIN
                Table_A a
                ON a.id = b.FK_toTableA';

    ALTER TABLE Table_A DROP COLUMN internalID;
END

I guess because SQL Server complies the whole thing - even that stuff bypassed via conditional logic - before running the script.

Here's a good article about what sql_executesql does, compared to EXEC, but it basically boils down to more injection prevention.

Upvotes: 1

Related Questions