April 10, 2006 at 12:23 am
Hi all...
I have a procedure that has about ten insert querrys and i would like to know what is the best way to see if insert pros made it ok...
is it best to do a IF(Error <>0) THen goto error
or is there a better way to make sure that all inserts worked and it is ok to commit?
thanks for any help
erik
Dam again!
April 10, 2006 at 12:05 pm
you can either put all the inserts in one transaction and check for errors after each T-SQL statement and return if there's an error or use a transaction for each.
Something along these lines:
BEGIN TRAN T1
INSERT INTO table1 (...) VALUES (...)
IF @@ERROR 0
BEGIN
RAISERROR...
ROLLBACK TRAN T1
RETURN -1
END
INSERT INTO table2 (...) VALUES (...)
IF @@ERROR 0
BEGIN
RAISERROR...
ROLLBACK TRAN T1
RETURN -1
END
.....
COMMIT TRAN T1
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 11, 2006 at 6:31 am
If you're working in 2005 you can also use Try/Catch...
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO table1 (...) VALUES (...)
INSERT INTO table2 (...) VALUES (...)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR...
ROLLBACK TRANSACTION
RETURN -1
END CATCH
April 11, 2006 at 8:13 am
Thats nice... will have to give that a shot...
Dam again!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply