Transaction within IF THEN ELSE doesn't commit

Posted by boris callens on Stack Overflow See other posts from Stack Overflow or by boris callens
Published on 2010-04-26T13:36:58Z Indexed on 2010/04/26 13:43 UTC
Read the original article Hit count: 195

Filed under:
|

In my TSQL script I have an IF THEN ELSE structure that checks if a column already exists.
If not it creates the column and updates it.

IF NOT EXISTS(
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'tableName' AND COLUMN_NAME = 'columnName'))
BEGIN
    BEGIN TRANSACTION
        ALTER TABLE tableName
        ADD columnName int NULL
    COMMIT
    BEGIN TRANSACTION
        update tableName
        set columnName = [something]
        from 
            [subquery]
    COMMIT
END

This doesn't work because the column doesn't exist after the commit.
Why doesn't the COMMIT commit?

© Stack Overflow or respective owner

Related posts about sql

Related posts about transactions