SQL如何在Where子句中包括并排除零值

Question

我的SSR中有一个@AgentGroup参数，该参数使用sp。

我要做的是，如果参数传递值PREPB_STRSTR,NULL，它也将显示为空值。我考虑过在我的Where子句中添加OR @AgentGroup is null，但是如果参数仅通过一个值PREPB_STRSTR，它也会显示null数据。

DECLARE @DateTo date = '3/21/2017',
@DateFrom date = '3/21/2017',
@VHGroup nvarchar(max) = 'PREPB_STRSTR,NULL'

  SELECT 
  [AgentIRACGroup]
  ,[CallOutCallID]
  ,[CallOutDateTime]
  ,[OutboundDuration]
  ,[CallOutNumber]
  ,[AgentName]
  ,[CallOutAgentID]
  ,[AgentGroup]
  ,[Outcome]
  ,[TalkTime]
   FROM [AgentStatisticsEOD].[dbo].[IRAC_Data]
   WHERE CONVERT(Date,[SCBCallDateTimeRequest]) BETWEEN @DateFrom AND @DateTo 
   AND [AgentGroup] collate database_default in (SELECT value from dbo.FnSplit(@VHGroup,',')) OR [AgentGroup] is null

我的查询将始终显示为null [AgentGroup]，即使@Agentgroup也不通过空值。

Answer 1

求解它的最简单方法是添加另一个条件以检查参数是否实际包含 NULL：

DECLARE @DateTo date = '3/21/2017',
@DateFrom date = '3/21/2017',
@VHGroup nvarchar(max) = 'PREPB_STRSTR,NULL'

SELECT 
[AgentIRACGroup]
,[CallOutCallID]
,[CallOutDateTime]
,[OutboundDuration]
,[CallOutNumber]
,[AgentName]
,[CallOutAgentID]
,[AgentGroup]
,[Outcome]
,[TalkTime]
 FROM [AgentStatisticsEOD].[dbo].[IRAC_Data]
 WHERE CONVERT(Date,[SCBCallDateTimeRequest]) BETWEEN @DateFrom AND @DateTo 
 AND [AgentGroup] collate database_default in (SELECT value from dbo.Split(@VHGroup,',')) 
 OR 
 (',' + @VHGroup +',' LIKE '%,NULL,%' 
  AND [AgentGroup] is null
 )