Friday, August 16, 2013

TRY...CATCH, ROLLBACK, COMMIT IN SQL SERVER 2008

TRY-CATCH
BEGIN TRY
INSERT INTO TAB1
SELECT 1,'AAA',10
UNION ALL
SELECT 2,'BBB',20
UNION ALL
SELECT 3,'CCC',30
UNION ALL
SELECT 4,'DDD',40
UNION ALL
SELECT 5,'EEE',50
END TRY

BEGIN CATCH
RAISERROR('ERROR: PLEASE VERIFY',16,1)  
END CATCH

ROLLBACK
BEGIN TRY
BEGIN TRAN TRN1
INSERT INTO TAB1
SELECT 1,'AAA',10
UNION ALL
SELECT 2,'BBB',20
UNION ALL
SELECT 3,'CCC',30
UNION ALL
SELECT 4,'DDD',40
UNION ALL
SELECT 5,'EEE',50
END TRY

BEGIN CATCH
ROLLBACK TRAN TRN1
END CATCH

COMMIT
BEGIN TRAN TRN1
INSERT INTO TAB1
SELECT 1,'AAA',10
UNION ALL
SELECT 2,'BBB',20
UNION ALL
SELECT 3,'CCC',30
UNION ALL
SELECT 4,'DDD',40
UNION ALL
SELECT 5,'EEE',50
COMMIT TRAN TRN1

ROLLBACK-COMMIT
BEGIN TRY
BEGIN TRAN TRN1
INSERT INTO TAB1
SELECT 1,'AAA',10
UNION ALL
SELECT 2,'BBB',20
UNION ALL
SELECT 3,'CCC',30
UNION ALL
SELECT 4,'DDD',40
UNION ALL
SELECT 5,'EEE',50
COMMIT TRAN TRN1
END TRY

BEGIN CATCH
ROLLBACK TRAN TRN1
END CATCH

No comments:

Post a Comment