SQL Server transaction with custom template

Question

I would ask a basilar thing, I would use the native transaction stuffs to rollback a bunch of tables if some conditions are verified.

I really have to check how many updates results based of some stored procedure that gather data from tables populated by input reports, too less changes or too many changes means rollback because we will reject the reports. I would implements this feature with the native transaction of T-SQL, this is a pseudo of my idea:

CREATE PROCEDURE mytest 
AS
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        EXEC foo
        EXEC bar
        EXEC baz
    END TRY

    IF true
       COMMIT TRANSACTION
    ELSE
       ROLLBACK TRANSACTION
END

Of course, this is not working because of incorrect syntax near the keyword 'IF', maybe because it's not the right way to use transaction.

Fixed this I will think how to count the changes of a table, if need to use a temporary table or if I can use some SQL features. I'm search for docs or example but I'm, not finding anything.

Any hints?

question from:https://stackoverflow.com/questions/65906735/sql-server-transaction-with-custom-template

Answer 1

Your pseudo-code is missing a catch block. Below is an example that includes the error handling and other improvements.

CREATE PROCEDURE mytest
AS
SET XACT_ABORT ON; --best practice with explict transactions in procs
BEGIN TRY
    BEGIN TRAN;
    EXEC foo;
    EXEC bar;
    EXEC baz;
    IF (<your-validation-succeeded-condition-here>)
        COMMIT;
    ELSE
        ROLLBACK;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;