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
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