SQL - Where 子句中的动态条件

Question

我有以下存储过程。我遇到的问题是“条件”参数。基本上每个条件都是它自己的列，所以它像这样传递 -

@Conditions = 'AND hcc_108 = 1 AND ...' 等

我正在尝试做这样的事情-

ALTER PROC [dbo].[GetPatientPanelList] 
(
@CareProviderId int=null, 
@Patient nvarchar(60)=null, 
@Conditions varchar=null,
@LocationId int=null

) 

AS

if @Conditions is null

SELECT *
FROM vw_patient_attributes t1
INNER JOIN STG_OSHODS_DW.osh_rpt.dim_member_care_measures t2
    ON t1.PatientID = t2.emr_id
WHERE
(t1.PreferredServiceLocationID = @LocationId OR @LocationId IS NULL)
AND (t1.CareProviderID = @CareProviderId OR @CareProviderId IS NULL)
AND (t1.FullName like '%' + @Patient + '%' OR @Patient IS NULL) 

else

SELECT *
FROM vw_patient_attributes t1
INNER JOIN STG_OSHODS_DW.osh_rpt.dim_member_care_measures t2
    ON t1.PatientID = t2.emr_id
WHERE
(t1.PreferredServiceLocationID = @LocationId OR @LocationId IS NULL)
AND (t1.CareProviderID = @CareProviderId OR @CareProviderId IS NULL)
AND (t1.FullName like '%' + @Patient + '%' OR @Patient IS NULL)
+ @Conditions  

我只需要根据参数填充最后一个 AND 条件。我知道“+”是语法错误，但我似乎无法找到实现它的方法。

谢谢!

更新

我试过动态 sql，但它一直说“命令成功完成)”

这是我当前的代码。我在一个单独的窗口中写了这个，首先让查询工作。

DECLARE @where nvarchar(50) = ' and hcc_18 = 1'
,@sql nvarchar(MAX) ,
@CareProviderId int=null, 
@Patient nvarchar(60)=null,
@LocationId int=null

set @sql = 'select *
FROM vw_patient_attributes t1
INNER JOIN STG_OSHODS_DW.osh_rpt.dim_member_care_measures t2
ON t1.PatientID = t2.emr_id
WHERE t1.PreferredServiceLocationID = IsNull('+ convert(varchar,@LocationId) +',t1.PreferredServiceLocationID)
AND (t1.CareProviderID = isnull(' + convert(varchar,@CareProviderId)+ ', t1.CareProviderID)
AND (t1.FullName like %' + @Patient + '% OR ' + @Patient + ' IS NULL)' + @where  

exec(@sql)

Answer 1

我修复了您上次更新中的语法问题。这应该工作...

DECLARE @where nvarchar(50) = ' and hcc_18 = 1'
,@sql nvarchar(MAX) ,
@CareProviderId int=null, 
@Patient nvarchar(60)=null,
@LocationId int=null

set @sql = 'select *
FROM vw_patient_attributes t1
INNER JOIN STG_OSHODS_DW.osh_rpt.dim_member_care_measures t2
ON t1.PatientID = t2.emr_id
WHERE t1.PreferredServiceLocationID = case when '+ convert(varchar(8),isnull(@LocationId,0)) +' = 0 then t1.PreferredServiceLocationID else ' + convert(varchar,isnull(@LocationId,0)) + ' end
AND (t1.CareProviderID = case when ' + convert(varchar,isnull(@CareProviderId,0)) + ' = 0 then t1.CareProviderID else ' + convert(varchar,isnull(@CareProviderId,0)) + ' end
AND (t1.FullName like ''%' + isnull(@Patient,'') + '%'' OR ' + isnull(@Patient,0) + '=0)' + @where  

print(@sql)
--exec(@sql)