查询条件使用配置表——SQL Server

有时需要知道填入的客户信息是否完整，可以将完整性条件写进sql代码中，但是，维护起来会比较麻烦。所以考虑写一张配置表，维护人员只需要改配置表的内容即可

现有如下问题，想得到某客户信息是否完整以及来呢西人信息完整的数量~

/****** Object:  StoredProcedure [dbo].[P_DJ_CustomInfo_Isornot_Complete]    Script Date: 2019/12/6 15:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create proc [dbo].[P_DJ_CustomInfo_Isornot_Complete]
as
declare @condition nvarchar(max)
declare @number nvarchar(max)

set @condition=(select new_parameter_value from new_configuration_parameters 
where new_parameter_name = 'account_info_valid_condition')
set @number=(select new_parameter_value from new_configuration_parameters 
where new_parameter_name = 'contact_info_valid_condition')

declare @sql nvarchar(max)

set @sql=N'

select  a.BusinessUnitId
       ,a.Name as Dept
       ,b.SystemUserId
       ,case when b.IsDisabled=0 then b.FullName 
	         when b.IsDisabled=1 then ''离职人员'' end as Salesman  -- 销售员
	   ,AccountBase.AccountId
	   ,AccountBase.name as AccountName  -- 客户
	   ,d.new_productline  -- 产品线id 
	   '
	set @sql=@sql+',case when '+@condition +' then ''是'' else ''否'' end as Isornotcomplete'
	set @sql=@sql+',case when '+@number +' then 1 else 0 end as CompleteNumber'
	set @sql=@sql+N'
from BusinessUnitBase a
left join SystemUserBase b on a.BusinessUnitId=b.BusinessUnitId  -- 部门id
left join AccountBase  on b.SystemUserId=AccountBase.OwnerId  -- 销售id
left join Contact on b.SystemUserId=Contact.OwnerId and AccountBase.AccountId=Contact.AccountId
-- 产品线分派表
left join new_productline_assignmentBase d on AccountBase.AccountId=d.new_customer  -- 客户id 
'
exec dbo.sp_executesql @sql
GO