Sql where 子句返回所有内容，以防过滤器为空 [英] Sql where clause to return everything in case filter is null
问题描述
我下面有一个 sql 表 -
I have a sql table below -
SrNo Name Value
1 A X1
2 B NULL
3 C X3
4 D X4
5 E NULL
6 F NULL
我正在尝试使用以下两个条件从表中获取所有记录 -a) 如果 '@Value' 列上的过滤器如下所示为空，则它应返回包括空在内的所有记录
I am trying to get the all the records from the table to with below two conditions - a) In case the filter on the '@Value' column is null as below then it should return all the records including null
BEGIN
Declare @Value varchar(50)
SET @Value = NULL
SELECT * from TestTable where Value = @Value
END
b) 如果提供的@Value 不是空的，那么它应该返回列(这肯定有效)
b) In case if the @Value is provided which is not null then it should return the column(This surely works)
我尝试实现以下条件
BEGIN
Declare @Value varchar(50)
SET @Value = NULL
SELECT * from TestTable where Value = IIF(@Value is NULL,Value,@Value)
END
但上面也删除了 Null 列.任何帮助或建议将不胜感激.
But above removes the Null columns as well . Any help or suggestion would be deeply appreciated.
推荐答案
典型的方法是使用 OR 取决于是否提供了参数:
The typical approach is to use an OR depending on whether the parameter was supplied:
WHERE (Value = @Value OR @Value IS NULL);
但是，这可能会有问题，因为您会根据第一次调用获得该查询的一个执行计划.这意味着，如果第一个调用指定 @Value = 1，并且这导致索引查找，则第二个调用将在参数为 NULL 时获得索引查找(并且您不会喜欢那个的表现).同样在另一个方向，第一个参数是 NULL，你将得到一个扫描，因为你要返回整个表，但是在第二次调用时，当你只想要一个(或很少)行时，你是仍然要进行扫描.
However, this can be problematic, because you get exactly one execution plan for this query based on the first call. This means that if the first call specifies @Value = 1, and this leads to an index seek, the second call is going to get an index seek when the parameter is NULL (and you're not going to like the performance of that). Similarly in the other direction, the first parameter is NULL, you're going to get a scan because you're returning the whole table, but then on the second call when you just want one (or very few) rows, you're still going to get a scan.
对此的典型反应是添加 OPTION (RECOMPILE);"！
The typical reaction to that is "add OPTION (RECOMPILE);"!
这很好，除非 Value 是唯一的，或者任何给定值的数量总是非常少，并且您不断地使用显式值调用查询，从而导致小的搜索，您'每次都重新编译该查询.并且每次需要扫描时，每次都需要重新编译该查询.
Which is great, except if Value is unique, or always has a very small number of any given value, and you are constantly calling the query with explicit values that lead to a small seek, you're recompiling that query every time for nothing. And every time you need the scan, you're recompiling that query every time for nothing also.
这个厨房水槽"的妥协带有可选搜索参数的查询类型是使用动态 SQL(以不可注入的方式执行此操作很重要):
The compromise for this "kitchen sink" type of query with optional search parameters is to use dynamic SQL (and it's important to do this in a non-injectable way):
DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.TestTable';
IF @Value IS NOT NULL
BEGIN
SET @sql += N' WHERE Value = @Value';
END
SET @sql += N';';
EXEC sys.sp_executesql @sql, N'@Value varchar(50)', @Value;
现在您有两个不同的查询，它们将在两种不同的场景中以可预测的方式执行并且不需要重新编译.如果您有可能倾斜的数据(Value 的某些值可以更好地作为扫描或不同的计划形状)，那么您可以重新编译在这种情况下:>
Now you have two different queries that will perform predictably in the two different scenarios and don't need to be recompiled. If you have data that can skew (some values of Value that would work better as a scan or different plan shape), then you can recompile just in that case:
SET @sql += N' WHERE Value = @Value OPTION (RECOMPILE)';
如果您知道有特殊值会导致或不会导致问题，或者如果您有多个参数并且某些参数与唯一列冲突而其他参数不会，则您甚至可以执行此操作.
You can even do this if you have special values you know do or don't lead to the problem, or if you have multiple parameters and some go against unique columns and others don't.
更多信息:
这篇关于Sql where 子句返回所有内容，以防过滤器为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
