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: 236
        
sql
|transactions
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