Sql Server存储过程查询记录再判断字段是否为空

案例一：

 alter procedure Pro_EmpIsNull
@PS_DD_ReportOID uniqueidentifier
--@out_Message nvarchar(128) output
as
begin	 
	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=ActEvaEmpCount ,
		  @AvgReportCostTime=AvgReportCostTime ,@AvgResCostTime=AvgResCostTime ,@GoodCount=GoodCount ,@MidCount=MidCount ,@BadCount=BadCount
		  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 = '')--参评人员是否为空
			   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 Report.PS_DD_ReportOID=ResEmp.PS_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 Report.PS_DD_ReportOID=ResEmp.PS_DD_Report_FK
				where PS_DD_ReportOID=@PS_DD_ReportOID
				and (  ResEmp.ReportBeginTime is null
				 or	ResEmp.ReportEndTime is null
				 or ResEmp.ResBeginTime  is null
				or ResEmp.ResEndTime 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 Report.PS_DD_ReportOID=QueEmp.PS_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 Report.PS_DD_ReportOID=QueEmp.PS_DD_Report_FK
				where PS_DD_ReportOID=@PS_DD_ReportOID
				and ( isnull(QueEmp.ResEmpNo,'')=''
					or isnull(QueEmp.QueType,'')=''
					)
			)
			begin
				select '该记录提问人员列表填写不完善，请填写完善再提交！（提问人类型或答辩人工号不能为空）' as Out_Message;
				return;
			end
			

			
	end

end

案例二：

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= ResEmp.ReportBeginTime,@ReportEndTime=ResEmp.ReportEndTime,@ResBeginTime=ResEmp.ResBeginTime,@ResEndTime=ResEmp.ResEndTime,@ResEmpNo=QueEmp.ResEmpNo,@QueType=QueEmp.QueType from PS_DD_Report Report 
join  PS_DD_QueEmp  QueEmp with(nolock) on Report.PS_DD_ReportOID=QueEmp.PS_DD_Report_FK
join PS_DD_ResEmp ResEmp with(nolock) on Report.PS_DD_ReportOID=ResEmp.PS_DD_Report_FK
where 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)
AS
BEGIN
	Declare @ChkUser varchar(200)
	 select @ChkUser=
  (
  select PS_DDCL_FlowNodeEmp.EmpNo+'_'+d.EmpName+',' from PS_DDCL_FlowNodeEmp  --PS_DDCL_FlowNodeEmp.EmpNo+'_'+d.EmpName+','=拼接起来 类似（XX_XX,）
  Left join PS_DDCL_FlowNode a on PS_DDCL_FlowNodeEmp.PS_DDCL_FlowNode_FK=a.PS_DDCL_FlowNodeOID 
  left join PS_DDCL_Flow b on b.PS_DDCL_FlowOID=a.PS_DDCL_Flow_FK
 left join PS_DDCL_DealWith c on b.BillNo=c.BillNo
 left join PS_BS_Employee d on d.EmpNo=PS_DDCL_FlowNodeEmp.EmpNo
 where c.BillNo=PS_DDCL_DealWith.BillNo and PS_DDCL_FlowNodeEmp.IsCompleted=0 and IsCurrentNode=1
  FOR XML PATH('')--相当于把查询出来的多条记录一条条值循（然后赋值到上面，然后追加到@ChkUser变量里类似vs中的+=运算符）
  )
 from PS_DDCL_DealWith 
where 
PS_DDCL_DealWith.BillNo=@BillNo

	RETURN  @ChkUser 

END

查询和根据虚拟字段条件删除

--下面两个同一样的查询效果
select * from dbo.PM_OrderFiles where OrderNo ='W121090747755' and ItemsNo = '020' order by OrderNo+ItemsNo
select *from dbo.PM_OrderFiles where OrderNo+right('000'+ItemsNo,3)='W121090747755020'


--根据虚拟字段作为条件删除
delete PlanInfo where PlanID in
(
	select PlanID from 
	(
		select p.PlanID,p.PlanNo+right('000'+p.ItemNo,3) as piNo,p.SaleCompanyNo,p.FactoryNo,p.TypeId,t.ClientCode,p.TechnologyCode,
			   (select count(c_partType) as cntPartID from FactoryParts f where c_fileif = '1' and f.PlanID = t.PlanID group by PlanID) as FactoryParts,p.CreateDTime,
			t.TransferStartDTime,t.TransferEndDTime,t.TransferStatus,t.TransferLog,t.ProcessStartDTime,t.ProcessEndDTime,
			t.ProcessStatus,t.ProcessLog,p.FeedbackSaleResult,p.FeedbackSaleDTime,p.FeedbackProducingResult,p.FeedbackProducingDTime--,p.filenameheader
		  from PlanInfo p,TaskQueue t
		 where 1=1 and p.PlanID = t.PlanID
		   and p.IsFeedbackProducing = 0
		   and t.DirectoryOrFile = 'Directory'
		   --order by CreateDTime asc
	) as tt
where tt.FactoryParts is null
)