自己写的第一个存储过程

本文链接：https://blog.csdn.net/xie_xiansheng/article/details/52624035

在做公司项目的时候，完成一个页面操作后改变其它标准资料的某些字段，自己学着使用存储过程将之完成下来，为自己写的第一个存储过程作个纪念，希望之后自己能越学越好。

create procedure P_CustStatus
@CustID varchar(15),           --定义传入参数
@FBillNo varchar(30)
as
begin
declare @MAPStatu varchar(5),@CompNo varchar(5),@CheckNo varchar(5), --定义变量：MAP状态，外部投诉次数，内部抽检次数
   @IllgealNo varchar(5),@BlackNo varchar(5),@SApplyNo varchar(5)      --违规次数，黑名单次数，特殊申请次数
   if exists(select 1 from YEA_T_ER_SMRP where F_YEA_AGENTID= @CustID)    --如果是SMRP
       begin
           select @CompNo=count(F_YEA_SMRPSource) from YEA_T_ER_SMRP where F_YEA_AGENTID=@CustID and F_YEA_SMRPSource='外部投诉'     --select赋值
           select @CheckNo=count(F_YEA_SMRPSource) from YEA_T_ER_SMRP where F_YEA_AGENTID=@CustID and F_YEA_SMRPSource='内部抽检'
           select @IllgealNo=count(F_YEA_IsEvil) from YEA_T_ER_SMRP where F_YEA_AGENTID=@CustID and F_YEA_SpecilApply='1'
           select @SApplyNo=count(F_YEA_IsEvil) from YEA_T_ER_SMRP where F_YEA_AGENTID=@CustID and F_YEA_SpecilApply='1'
           update T_BD_CUSTOMER set
               F_YEA_ComplaintsNum = @CompNo,
               F_YEA_CheckNum = @CheckNo,
               F_YEA_IllegalNum = @IllgealNo,
               F_YEA_SApplyNum = @SApplyNo
           where FCUSTID = @CustID
       end
   else                                    --如果是MAP
           select @MAPStatu=F_YEA_MAPSOURCE from YEA_T_ER_MAP where F_YEA_STORENMAES=@CustID and FBILLNO=@FBillNo
           select @IllgealNo=count(F_YEA_MAPSOURCE) from YEA_T_ER_MAP where F_YEA_STORENMAES=@CustID and F_YEA_MAPSOURCE='B'
           select @BlackNo=count(F_YEA_MAPSOURCE) from YEA_T_ER_MAP where F_YEA_STORENMAES=@CustID and F_YEA_MAPSOURCE='D'
           select @CompNo=count(F_YEA_MAPSOURCES) from YEA_T_ER_MAP where F_YEA_STORENMAES=@CustID and F_YEA_MAPSOURCES='1'    --外部投诉
           select @CheckNo=count(F_YEA_MAPSOURCES) from YEA_T_ER_MAP where F_YEA_STORENMAES=@CustID and F_YEA_MAPSOURCES='2'   --内部检查
           update YEA_T_ER_OnlineStore set
               F_YEA_MAPSTATUS = @MAPStatu,
               F_YEA_CHECKNUM = @CheckNo,
               F_YEA_COMPLAINTSNUM = @CompNo,
               F_YEA_ILLEGALNUM = @IllgealNo,
               F_YEA_BLACKNUM = @BlackNo
           where FID = @CustID
end