>>I found that it should be
>>

>>-- If the error renders the transaction as uncommittable, we must rollback
>>		IF XACT_STATE() <> 0
>>            BEGIN
>>            --print 'Rollback transaction'
>>			ROLLBACK TRANSACTION
>>            END
>>
>>       RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState, @ErrorNumber)

>>
>>I already implemented the code in few procedures, now I would have to update them all :) Trust, but check! (Doverjaj, no proverjaj).
>
>When I read BOL about XACT_STATE() (because I try to wrote all my queries for 2000 :o( it will return you if the transaction is commitable (1) or not (-1), so maybe, just maybe you need this:
>

>IF XACT_STATE() < 0
>   ROLLBACK TRANSACTION
>ELSE IF XACT_STATE() > 0
>     COMMIT TRANSACTION
>

>But all depends what you want when you raise error.

You definetely don't want to commit transaction in case of an error. You want to ROLLBACK the transaction.

Let me read up on the subject as well, because I need to be able to find out the transaction level, etc.

Or am I thinking too much VFP-style and it handled differently in SQL Server? We should try to manually ROLLBACK in case of the error, correct?

E.g. I have right now

 BEGIN TRY
    BEGIN TRANSACTION
      ....
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF XACT_STATE<>0
    ROLLBACK TRANSACTION
  RAISEERROR()
END CATCH

Does it sound correct to you or how should I augment my logic?

Thanks again.

Or may be all statements in SP executed as one batch anyway and if one UPDATE fails, none would be updated?