What's wrong with that SQL syntax?

Question

I'm told to make some changes to the schema of our database, so I wrote the following script that does the following:

Update the value of the table, according the the value of another table
Delete the column that contains the id pointing to the other table

The entire script should only be executed if the id_to_other_table exists of course

Here is my SQL

IF EXISTS (
SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'title_id' AND TABLE_NAME = 'my_table'
)
BEGIN   
    UPDATE
        my_table
    SET
        my_table.title = other_table.value
        FROM
        my_table
        INNER JOIN
        other_table
    ON
        my_table.title_id = other_table.id
    WHERE other_table.language_id = 1 --hardcoded

    ALTER TABLE my_table
    DROP COLUMN title_id
END

The first time I run this SQL, the code is executed properly. When I run it once more, instead of doing nothing, it gives me the following error, pointing to the line after the ON part of the JOIN:

Invalid column name 'my_table.title_id'

If I replace the contents of the BEGIN - END block with print "Does the column exists?" it correctly displays nothing, so the BEGIN - END block is ending preemptively for some reason...

Any ideas?

It's not supposed to join it if it's not there, yet it tried. That was the point of the question — Loupax
– Loupax, Commented Oct 25, 2013 at 8:02

blowdoof · Accepted Answer · 2013-10-16 11:03:54Z

Actually, the error is generated because you are executing multiple sql statements inside the block (a batch). As soon as you have a batch, some rules apply. Since you are ALTERING and referencing the same table inside 1 batch, the compile error occurs. As soon as you seperate the both sql statements, you won't get a compile error:

    IF EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME = 'title_id' AND TABLE_NAME = 'my_table'
    )
    BEGIN   
        UPDATE
            my_table
        SET
            my_table.title = other_table.value
            FROM
            my_table
            INNER JOIN
            other_table
        ON
           my_table.title_id = other_table.id
        WHERE other_table.language_id = 1 --hardcoded

        END

    IF EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME = 'title_id' AND TABLE_NAME = 'my_table'
    )
    BEGIN   
        ALTER TABLE my_table
        DROP COLUMN title_id
    END

podiluska · Accepted Answer · 2013-10-16 10:33:56Z

0

The SQL is parsed as a whole, before execution. So despite the conditional statements not being run, they're still checked for validity. You could encapsulate the commands as strings and use exec or sp_executesql to run them, as a workaround.

answered Oct 16, 2013 at 10:33

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Collectives™ on Stack Overflow

What's wrong with that SQL syntax?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related