SQL SERVER 事务处理通用模板

-- =============================================
-- Transaction Temp
-- =============================================
BEGIN TRY
    BEGIN TRANSACTION
 
    --
    -- You code here.
    --
 
    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    IF (@@TRANCOUNT > 0)
        -- Adds store procedure
        -- Writes the error into ErrorLog table.
        ROLLBACK TRANSACTION
    END IF
END CATCH

例如：

BEGIN TRY
        BEGIN TRANSACTION
                PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('Cris', 1);
                EXEC SPAddDataToUserInfo
                INSERT INTO UserInfo VALUES('Ada', 32);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION
        PRINT 'Rolled back successful in SPMultiDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
    END CATCH
   
END