Create a table:
Set Ansi_nulls On
Go
Set Quoted_identifier On
Go
Create Table [ DBO ] . [ T1 ] (
[ ID ] [ Int ] Not Null ,
[ C1 ] [ Nvarchar ] ( 50 ) Null ,
[ C2 ] [ Datetime ] Null ,
Constraint [ Pk_table1 ] Primary Key Clustered
(
[ ID ] ASC
) With (Pad_index = Off , Statistics_norecompute = Off , Ignore_dup_key = Off , Allow_row_locks = On , Allow_page_locks = On ) On [ Primary ]
) On [ Primary ]
Solution (1)
Declare @ Ierrorcount Int
Set @ Ierrorcount = 0
Begin Tran Tran_2008_10_07
insert into T1 (ID, c1) values ( 1 , ' 1 ' )
set @ ierrorcount = @ ierrorcount + @@ error
insert into T1 (ID, c1) values ( 2 , ' 2 ' )
set @ ierrorcount = @ ierrorcount + @@ error
insert into T1 (ID, c1) values ( ' xxxx3 ' , ' 3 ' )
set @ ierrorcount = @ ierrorcount + @@ error
insert into T1 (ID, c1) values ( 4 , ' 4 ' )
set @ ierrorcount = @ ierrorcount + @@ error
Insert IntoT1 (ID, C1)Values(5,'5')
Set @ Ierrorcount=@ Ierrorcount+@ Error
If @ Ierrorcount = 0
Begin
Commit Tran Tran_2008_10_07
End
Else
Begin
Rollback Tran Tran_2008_10_07
End
Solution (2)
Begin Try
Begin Tran Tran_2008_10_07
Insert IntoT1 (ID, C1)Values(1,'1')
insert into T1 (ID, c1) values ( 2 , ' 2 ' )
insert into T1 (ID, c1) values ( ' xxxx3 ' , ' 3 ' )
insert into T1 (ID, c1) values ( 4 , ' 4 ' )
insert into T1 (ID, c1) values ( 5 , ' 5 ' )
commit Tran tran_2008_10_07
end try
begin catch
raiserror ' error '
rollback Tran tran_2008_10_07
end catch