存储过程的写法

 

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