預存程序的寫法

 

CREATE PROCEDURE dbo.sp_consume_create
--月卡，年卡或次卡消費過程
@card_code nvarchar(50),  --會員卡號
@csm_cus_name nvarchar(50), --客戶帳號
@card_type int, --卡類型
@csm_csmItems int, --消費項目
@csm_coach nvarchar(50),--1對1服務人員
@cms_operator nvarchar(50),--操作人員
@cms_company int, --分店/分公司編號
@cms_remarks nvarchar(100), --消費備忘
---------------------------------------------------------------------
@card_points int  -- 積分

AS
 begin transaction
     begin   --增加一條消費記錄
     insert into tb_consumeRcd ( csm_card_code,csm_cus_name,csm_csmItems,csm_coach,cms_operator,cms_company,cms_remarks )

     values (@card_code,@csm_cus_name,@csm_csmItems,@csm_coach,@cms_operator,@cms_company,@cms_remarks)
     if @@ROWCOUNT=0
         return 0
     else
         begin --修改會員卡次數和增加積分
         if @card_type = 1 --表示是次卡 更新次數和積分
             update tb_cardInfo set card_times=card_times-1,card_useTimes=card_useTimes+1,card_points=card_points+@card_points,card_usePoints=card_usePoints+@card_points where
card_code=@card_code
         else --表示是按時間計算的卡 只更新積分
             update tb_cardInfo set
card_points=card_points+@card_points,card_usePoints=card_usePoints+@card_points where
card_code=@card_code
         end
     end

 if @@error>0
     begin
         --print '復原事務.'
         rollback transaction
     end
 else
     begin
         --print '提交事務'
         commit transaction
     end
GO