Tell me more ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 3 down vote favorite

Can someone help me refactor this code so in case of an error nothing gets persisted to the db? this doesn't work when an error occurs.

use mydb

Begin Transaction 


SET IDENTITY_INSERT table1 On
Insert into table1 (...) values (...)
SET IDENTITY_INSERT table1 Off
If @@error <> 0 GOTO done

SET IDENTITY_INSERT table2 On
Insert into table2 (..) values (...)
SET IDENTITY_INSERT table2 Off
If @@error <> 0 GOTO done

Commit Transaction
Return

done:
Rollback Transaction
Return
share|improve this question

migrated from stackoverflow.com Aug 19 '11 at 16:24

4 Answers

up vote 5 down vote accepted

With SQL Server 2005 onwards you have got better approach:

BEGIN TRY
   Begin Transaction
   Try Statement 1
   Try Statement 2
   ...
   Try Statement M
   Commit Transaction
END TRY
BEGIN CATCH
   Rollback Transaction
END CATCH

More explained here: http://www.4guysfromrolla.com/webtech/041906-1.shtml

share|improve this answer
2  
+1, but put the Begin Transaction inside the BEGIN TRY – KM Aug 18 '11 at 14:44
up vote 1 down vote

@@error is not equal to zero if the last statement returned with an error (see MSDN) -- in your case this would be SET IDENTITY_INSERT ... OFF. 

use mydb

Begin Transaction 


SET IDENTITY_INSERT table1 On
Insert into table1 (...) values (...)
If @@error <> 0 GOTO done
SET IDENTITY_INSERT table1 Off

SET IDENTITY_INSERT table2 On
Insert into table2 (..) values (...)
If @@error <> 0 GOTO done
SET IDENTITY_INSERT table2 Off

Commit Transaction

Return

done:
SET IDENTITY_INSERT table1 Off
SET IDENTITY_INSERT table2 Off

Rollback Transaction
Return
share|improve this answer
you can not have 2 tables with Identity insert ON at the same time. Please refer msdn.microsoft.com/en-us/library/ms188059.aspx – DipakRiswadkar Aug 18 '11 at 14:45
@DipakRiswadkar sorry of course, but only a little edit :) – Matten Aug 18 '11 at 15:58
up vote 1 down vote

One more thing:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for

share|improve this answer
up vote 3 down vote

Instead of a GOTO format, wrap your queries + commit in a TRY CATCH block.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.