News

Copyright © 2008-2018 Paula DiTallo

Tag Cloud



mssql2008+ : How do I manage transactions in stored procedures?

This pattern works well:

USE [yourDatabase]
GO

IF OBJECT_ID('ThisSproc', 'P') IS NOT NULL
DROP PROCEDURE ThisSproc;
GO

CREATE PROCEDURE ThisSproc
  @PersonId INT,
  @NewMoneyCollected MONEY = 0
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE OrganizationTripFunding
      SET Balance = MoneyCollectedAllYear + @NewMoneyCollected
      WHERE PersonId = @PersonID;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
    
-- use as debugging tool -- PRINT 'error number: ' + CAST(@ErrorNumber AS VARCHAR(10)); -- PRINT 'line number: ' + CAST(@ErrorLine AS VARCHAR(10)); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END; GO


Monday, March 16, 2015 8:39 PM

Feedback

No comments posted yet.


Post A Comment
Title:
Name:
Email:
Comment:
Verification: