带有事务的存储过程的写法

存储过程 SET NOCOUNT ON：

当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。\

当 SET NOCOUNT 为 OFF 时，返回计数。

如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。

 

 

 

带有事务的存储过程的写法
CREATE PROCEDURE [dbo].[UP_UpdateSOtoBePayed]
(     
    @SONO varchar(30),
    @BankNO varchar(50),
    @TradeNO varchar(50),
    @TradeDate datetime,
    @PayAmount decimal(18,2),
    @PayFee decimal(18,2)
)
AS
BEGIN
    SET NOCOUNT ON;--提高性能的，必须要有
    DECLARE @Now datetime;
    SET @Now=getdate();--所有操作保证统一时间
    BEGIN TRY
       BEGIN TRANSACTION myTrans;--开始事务
       IF EXISTS(SELECT TOP 1 ID FROM dbo.SOPaymentTransaction WHERE SONO=@SONO AND Status=1)
       BEGIN
           ROLLBACK TRANSACTION myTrans;--回滚事务
           RETURN;
       END
      
       UPDATE dbo.SOMaster
           SET SaleOrderStatus=5,EditUser='system',EditDate=@Now
       WHERE RelatedSONO=@SONO;
      
       IF NOT EXISTS(SELECT TOP 1 ID FROM Accounting.dbo.TransactionData WHERE SONO=@SONO)
       BEGIN
           INSERT INTO Accounting.dbo.TransactionData
           (
              TransactionType ,
              TransactionAmount ,
              TransactionFee,
              InOrOut ,
              SONO ,
              CustomerName ,
              MerchantID ,
              TransactionNO ,
              TransactionDate ,
              PaymentWayID ,
              PaymentWayName ,
              BankNO,
              UserIP ,
              CreateUser ,
              CreateDate ,
              EditUser ,
              EditDate ,
              Status
           )
           SELECT
              1,
              SOM.OrderTotalAmount,
              @PayFee*(SOM.OrderTotalAmount/@PayAmount),
              0,
               SOM.SONO ,
              SOM.CustomerName,
              SOM.MerchantID,
              @TradeNO,
              @TradeDate,
              SOPT.PaymentID,
              SOPT.PaymentName,
              @BankNO,
              SOPT.UserIP,
              'juhaoo',
              @Now,
              'juhaoo',
              @Now,
              3
           FROM dbo.SOPaymentTransaction SOPT
           LEFT JOIN dbo.SOMaster SOM ON SOM.RelatedSONO=SOPT.SONO
           WHERE SOPT.SONO=@SONO;
       END
      
       UPDATE dbo.SOPaymentTransaction
       SET
           BankNO =@BankNO ,
           TradeNO =@TradeNO ,
           TradeDate =@TradeDate,
           PayAmount =@PayAmount,
           PayFee =@PayFee,
           EditDate = @Now,
           Status=1
       WHERE SONO=@SONO;
      
       COMMIT TRANSACTION myTrans;--事务提交语句
      
    END TRY
    BEGIN CATCH
       ROLLBACK TRANSACTION myTrans-- 始终回滚事务
      
       --抛出异常
       DECLARE @ErrorMessage NVARCHAR(4000);
       DECLARE @ErrorSeverity INT;
       DECLARE @ErrorState INT;
       SELECT
           @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
       RAISERROR(@ErrorMessage,  -- Message text.
                 @ErrorSeverity, -- Severity.
                 @ErrorState     -- State.
                 );
   END CATCH
END