Hi Dimitry

you can THROW an error in your test that will go to the CATCH block and your COMMIT is then end of the TRANSACTION block

Nick

>Hi,
>This is a very newbie question of SQL Server Stored Procedure (I am using SQL Server 2008)
>
>The following is a pseudo code of a SQL Server procedure
>

>BEGIN TRY
>     BEGIN TRANSACTION
>         update MyTable1 set Field1 = @SomeValue where Field2 = @SomeValue2
>         IF @@ROWCOUNT = 0
>         BEGIN
>               -- at this point I want to Cancel the Transaction and return.  How?
>         END
>
>         update MyTable2 Set ....
>
>         insert into MyTable3 ....
>
>        COMMIT
>END TRY
>
>BEGIN CATCH
>      IF @@TRANCOUNT > 0
>          ROLLBACK
>END CATCH
>

>
>I have a couple of questions:
>1. In the code above, on the first update I want to cancel if no record is found to be updated. How?
>2. Is there a command END TRANSACTION is TSQL?
>
>TIA