SSRS 参数。允许“全部”或“空”

Question

SSRS 参数很痛苦。我希望能够通过允许用户访问许多不同的参数并使它们成为可选参数来重复使用报告来满足许多不同的需求。

因此，如果我从以下代码开始：

Select * from mytable myt
where myt.date between '1/1/2010' and '12/31/2010'
and year(myt.date) = '2010'
and myt.partnumber = 'XYZ-123' 

我希望这些参数是可选的，因此我的第一次尝试是将参数默认为 null，例如：

and (myt.partnumber = (@PartNumber) or (@PartNumber) is null)

这有问题，因为如果有问题的数据库字段可为空，那么您将删除记录因为 null 不等于 null。

然后我使用了这样的代码：

DECLARE @BeginDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @PartNumber AS VARCHAR(25)
SET @Year = '..All'
SET @BeginDate = '1/1/2005'
SET @EndDate = '12/31/2010'
SET @PartNumber = '..All'
SET @Year = '..All'

Select * from mytable myt
where (myt.date between (@BeginDate) and (@EndDate))
and (year(myt.date) =  (@Year) or (@Year) = '..All' )
and (myt.partnumber = (@PartNumber) or (@PartNumber) = '..All')

这不起作用，因为 Year(myt.date) 是一个整数，而 @Year 不是。

所以，这是我的问题。

1. 如何让我的日期可选？最好的方法是将它们默认为实际范围之外的日期，以便我返回所有值？
2. 处理 null 或“..All”选项以使我的查询尽可能可读并允许我的用户拥有大多数数据类型的可选参数的最佳方法是什么？我宁愿不使用 null

Answer 1

继续并允许空值，这表明不应应用过滤器。然后，您可以使用以下内容：

SELECT *
FROM mytable myt
WHERE COALESCE(myt.date, '1/1/1900') between COALESCE(@BeginDate, myt.date, '1/1/1900') and COALESCE(@EndDate, myt.date, '1/1/1900')
    AND COALESCE(YEAR(myt.date), -1) = COALESCE(@Year, YEAR(myt.date), -1)
    AND COALESCE(myt.partnumber, -1) = COALESCE(@PartNumber, myt.partnumber, -1)

总之，如果任何变量值为 NULL，则将列值与其自身进行比较，这实际上会忽略该条件。更具体地说，在测试 myt.date 时，如果 @BeginDate 为 NULL，则将下限范围值设置为等于 myt.date 值。使用 @EndDate 值进行相同的替换。即使 @BeginDate 和 @EndDate 均为 NULL，条件也将为 true。

YEAR(myt.date) 和 myt.partnumber 使用类似的方法。如果变量值为 NULL，则将列值与其自身进行比较，这始终为 true。

更新：
为每个COALESCE添加了默认值，以处理列值为NULL的情况。

Answer 2

我喜欢你的第三个代码块。看来您的 WHERE 子句可以更正为使用非 int 值。年行的 AND 子句看起来像这样——不是我最好的 T-SQL，但它应该为您指明正确的方向：

and 1 = CASE @Year WHEN '..All' THEN 1 ELSE CASE WHEN year ( myt.date )  = CONVERT ( int, @Year ) THEN 1 ELSE 0 END END

这将允许您拥有字符串值 '..All' 或 int 值。两者都会正确匹配。您可以对零件编号执行相同的操作。

Answer 3

像这样尝试一下，关键是将空参数值修复为代理空值，而且由于sql server支持短路评估，因此进行空值检查通常应该表现得更好。

Select * from mytable myt
where (myt.date between (@BeginDate) and (@EndDate))
and (@Year IS NULL OR COALESCE(myt.date,'1900') = @Year)
and (@PartNumber IS NULL OR ISNULL(myt.partnumber, '<NULL>') = (@PartNumber)