存储过程实例

Create PROCEDURE [dbo].[spf_PaymentMode_CreateUpdateDelete]
 (
  @ModeId INT = NULL OUTPUT,
  @MerchantCode NVARCHAR(300) = NULL,
  @EmailAddress NVARCHAR(255) = NULL,
  @SecretKey NVARCHAR(4000) = NULL,
  @SecondKey NVARCHAR(4000) = NULL,
  @Password NVARCHAR(4000) = NULL,
  @Partner NVARCHAR(300) = NULL,
  @Name NVARCHAR(100) = null,
  @Description NVARCHAR(4000) = NULL,
  @Gateway NVARCHAR(200) = NULL,
  @DisplaySequence INT = NULL,
  @Charge MONEY = NULL,
  @IsPercent BIT = NULL,
  @Action INT,
  @Status INT OUTPUT
 )
AS
 SET @Status = 99
 
 IF @Action = 2 -- 删除
 BEGIN -- 同时删除支付方式的支付货币
 
  SET XACT_ABORT ON
  DECLARE @intErrorCode INT
  
  BEGIN TRAN
       DELETE FROM Hishop_PaymentCurrencys WHERE ModeId = @ModeId
       SET @intErrorCode = @@ERROR
  
       IF @intErrorCode = 0
          BEGIN
             DELETE FROM  Hishop_PaymentTypes  WHERE   ModeId = @ModeId   SET @intErrorCode = @@ERROR
          END
   
       IF @intErrorCode = 0
          BEGIN
              SET @Status = 0
             COMMIT TRAN
          END
      ELSE
         ROLLBACK TRAN
  
  RETURN
  END

 IF @Action = 0 -- 创建
     BEGIN
           --- 修改显示序号
         IF  @DisplaySequence IS NULL
              SET @DisplaySequence = (Select Max(DisplaySequence) From Hishop_PaymentTypes) + 1
        ELSE
              UPDATE Hishop_PaymentTypes set DisplaySequence = DisplaySequence + 1 where DisplaySequence >= @DisplaySequence
        INSERT INTO  Hishop_PaymentTypes(MerchantCode, EmailAddress, SecretKey, SecondKey, Password,  Partner, [Name], Description, Gateway, DisplaySequence, Charge, IsPercent)   VALUES    (@MerchantCode, @EmailAddress, @SecretKey, @SecondKey, @Password,    @Partner, @Name, @Description, @Gateway,   @DisplaySequence, @Charge, @IsPercent)
        SELECT @ModeId = @@IDENTITY
        IF @@ROWCOUNT = 1
        SET @Status = 0
  
  RETURN
 END

 IF @Action = 1 -- 修改
    BEGIN
  --判断序号是否为空
       IF  @DisplaySequence IS NUll
          SET @DisplaySequence = (Select Max(DisplaySequence) From Hishop_PaymentTypes) + 1
       Else IF @DisplaySequence != (Select DisplaySequence  From Hishop_PaymentTypes where ModeId=@ModeId)
          UPDATE Hishop_PaymentTypes set DisplaySequence = DisplaySequence + 1 where DisplaySequence >= @DisplaySequence
       UPDATE  Hishop_PaymentTypes  SET 

        MerchantCode = @MerchantCode,

        EmailAddress = @EmailAddress, 

       SecretKey = @SecretKey, 

       SecondKey = @SecondKey,  

       Password = @Password,  

       Partner = @Partner, 

       [Name] = @Name,  

       Description = @Description,
       Gateway = @Gateway,
       DisplaySequence = @DisplaySequence,
       Charge = @Charge,
      IsPercent = @IsPercent
  WHERE ModeId = @ModeId
  
  IF @@ROWCOUNT = 1
   SET @Status = 0
  
  RETURN
 END
GO