2023年7月13日发(作者：)

SqlServer存储过程查询记录再判断字段是否为空案例⼀： alter procedure Pro_EmpIsNull@PS_DD_ReportOID uniqueidentifier--@out_Message nvarchar(128) outputasbegin

begin declare @ReportBeginTime datetime,--⾃述开始时间 @ReportEndTime datetime,--⾃述结束时间 @ResBeginTime datetime,--答辩开始时间 @ResEndTime datetime,--答辩结束时间 @ResEmpNo varchar(128),--答辩⼈⼯号 @QueType int,--提问⼈类型

@EvaYear varchar(128),--年度 @EvaBeginDate datetime,--开始时间 @EvaEndDate datetime,--结束时间 @Approvers varchar(128),--审批⼈ @EvaEmps varchar(128),--参评⼈员 @ReportName varchar(128),--报告名称 @ReportNo varchar(128),--报告编号 @EvaGrpNo varchar(128),--考核组 @Leaders varchar(128),--参加领导 @EvaEmpCount int,--考评组⼈数 @ActEvaEmpCount int,--实际考评⼈数 @AvgReportCostTime numeric(10,1),--⼈均⾃述时间(Min/⼈) @AvgResCostTime numeric(10,1),--⼈均答辩时间(Min/⼈) @GoodCount int,--好 @MidCount int,--中 @BadCount int--差 --督导报告主表 select @EvaYear=EvaYear,@EvaBeginDate=EvaBeginDate,@EvaEndDate=EvaEndDate,@Approvers=Approvers,@EvaEmps=EvaEmps,

@ReportName=ReportName,@ReportNo=ReportNo ,@EvaGrpNo=EvaGrpNo ,@Leaders=Leaders,@EvaEmpCount=EvaEmpCount ,@ActEvaEmpCount=ActE from PS_DD_Report Report where PS_DD_ReportOID=@PS_DD_ReportOID if(@EvaYear = '')--年度是否为空 begin

select '年度不能为空！'as Out_Message; return; end if(@EvaBeginDate is null)--开始时间时间是否为空 begin

select '开始时间不能为空！'as Out_Message; return; end if(@EvaEndDate is null)--结束时间是否为空 begin

select '结束时间不能为空！'as Out_Message; return; end if(@Approvers = '')--审批⼈是否为空 begin

select '审批⼈不能为空！'as Out_Message; return; end if(@EvaEmps = '')--参评⼈员是否为空 @AvgReportCostTime=AvgReportCostTime ,@AvgResCostTime=AvgResCostTime ,@GoodCount=GoodCount ,@MidCount=MidCount ,@BadCount=BadCoun if(@EvaEmps = '')--参评⼈员是否为空 begin

select '参评⼈员不能为空！'as Out_Message; return; end if(@ReportName = '')--报告名称是否为空 begin

select '报告名称不能为空！'as Out_Message; return; end if(@ReportNo = '')--报告编号是否为空 begin

select '报告编号不能为空！'as Out_Message; return; end if(@EvaGrpNo = '')--考核组是否为空 begin

select '考核组不能为空！' as Out_Message; return; end if(@Leaders = '')--参加领导是否为空 begin

select '参加领导不能为空！' as Out_Message; return; end if(@EvaEmpCount is null)--考评组⼈数是否为空 begin

select '考评组⼈数不能为空！' as Out_Message; return; end if(@ActEvaEmpCount is null)--实际考评⼈数是否为空 begin

select '实际考评⼈数不能为空！'as Out_Message; return; end if(@AvgReportCostTime is null)--⼈均⾃述时间(Min/⼈)是否为空 begin

select '⼈均⾃述时间(Min/⼈)不能为空！' as Out_Message; return; end if(@AvgResCostTime is null)--⼈均答辩时间(Min/⼈)是否为空 begin

select '⼈均答辩时间(Min/⼈)不能为空！' as Out_Message; return; end if(@GoodCount is null)--好是否为空 begin

select '好不能为空！' as Out_Message; return; end

if(@MidCount is null)--中是否为空 begin

select '中不能为空！' as Out_Message; return; end

if(@BadCount is null)--差是否为空 begin

select '差不能为空！' as Out_Message; return; end -- 答辩⼈员列表 if not exists( select 1 from PS_DD_Report Report

join PS_DD_ResEmp ResEmp with(nolock) on _DD_ReportOID=_DD_Report_FK where PS_DD_ReportOID=@PS_DD_ReportOID ) begin select '该记录答辩⼈员列表时间填写不完善，请填写完善再提交！' as Out_Message; return; end if exists( select 1 from PS_DD_Report Report

join PS_DD_ResEmp ResEmp with(nolock) on _DD_ReportOID=_DD_Report_FK where PS_DD_ReportOID=@PS_DD_ReportOID and ( BeginTime is null or EndTime is null or inTime is null or Time is null ) ) begin select '该记录答辩⼈员列表时间填写不完善，请填写完善再提交！' as Out_Message; return; end

-- 提问⼈员列表

if not exists( select 1 from PS_DD_Report Report

join PS_DD_QueEmp QueEmp with(nolock) on _DD_ReportOID=_DD_Report_FK where PS_DD_ReportOID=@PS_DD_ReportOID ) begin select '该记录提问⼈员列表填写不完善，请填写完善再提交！（提问⼈类型或答辩⼈⼯号不能为空）' as Out_Message; return; end if exists( select 1 from PS_DD_Report Report

join PS_DD_QueEmp QueEmp with(nolock) on _DD_ReportOID=_DD_Report_FK where PS_DD_ReportOID=@PS_DD_ReportOID and ( isnull(No,'')='' or isnull(e,'')='' ) ) begin select '该记录提问⼈员列表填写不完善，请填写完善再提交！（提问⼈类型或答辩⼈⼯号不能为空）' as Out_Message; return; end

endend案例⼆：Create procedure Pro_EmpIsNull @PS_DD_ReportOID nvarChar(32) ----答辩⼈员表OID AS BEGIN

declare @ReportBeginTime varchar(128);--⾃述开始时间 declare @ReportEndTime varchar(128);--⾃述结束时间 declare @ResBeginTime varchar(128);--答辩开始时间 declare @ResEndTime varchar(128);--答辩结束时间 declare @ResEmpNo varchar(128);--答辩⼈⼯号 declare @QueType varchar(128);--提问⼈类型

select @ReportBeginTime= BeginTime,@ReportEndTime=EndTime,@ResBeginTime=inTime,@ResEndTime=join PS_DD_QueEmp QueEmp with(nolock) on _DD_ReportOID=_DD_Report_FKjoin PS_DD_ResEmp ResEmp with(nolock) on _DD_ReportOID=_DD_Report_FKwhere PS_DD_ReportOID=@PS_DD_ReportOID if(@ReportBeginTime = '' or @ReportEndTime = '' or @ResBeginTime = '' or @ResEndTime = '' or @ResEmpNo = '' or @QueType = '')--⾃述开始时间是否为空 begin

return '该记录资料填写不完善，请填写完善再提交！'; end END变量值函数:ALTER FUNCTION [dbo].[GetOperatorUser]( @BillNo varchar(20)

)RETURNS varchar(200)ASBEGIN Declare @ChkUser varchar(200) select @ChkUser= ( Left join PS_DDCL_FlowNode a on PS_DDCL__DDCL_FlowNode_FK=_DDCL_FlowNodeOID

left join PS_DDCL_Flow b on _DDCL_FlowOID=_DDCL_Flow_FK left join PS_DDCL_DealWith c on = left join PS_BS_Employee d on =PS_DDCL_ where =PS_DDCL_ and PS_DDCL_leted=0 and IsCurrentNode=1 FOR XML PATH('')--相当于把查询出来的多条记录⼀条条值循（然后赋值到上⾯，然后追加到@ChkUser变量⾥类似vs中的+=运算符） ) from PS_DDCL_DealWith

where

PS_DDCL_=@BillNo RETURN @ChkUser

END select PS_DDCL_+'_'+e+',' from PS_DDCL_FlowNodeEmp --PS_DDCL_+'_'+e+','=拼接起来查询和根据虚拟字段条件删除--下⾯两个同⼀样的查询效果select * from _OrderFiles where OrderNo ='W5' and ItemsNo = '020' order by OrderNo+ItemsNoselect *from _OrderFiles where OrderNo+right('000'+ItemsNo,3)='W121'--根据虚拟字段作为条件删除delete PlanInfo where PlanID in( select PlanID from

( select ,+right('000'+,3) as piNo,mpanyNo,yNo,,Code,logyCode, (select count(c_partType) as cntPartID from FactoryParts f where c_fileif = '1' and = group by PlanID) as FactoryParts,DTime, erStartDTime,erEndDTime,erStatus,erLog,sStartDTime,sEndDTime, sStatus,sLog,ckSaleResult,ckSaleDTime,ckProducingResult,ckProducingDTime--,meheader from PlanInfo p,TaskQueue t where 1=1 and = and backProducing = 0 and oryOrFile = 'Directory' --order by CreateDTime asc ) as ttwhere yParts is null)