处理“可选" SQL中的where子句过滤器的正确方法? [英] Proper way to handle 'optional' where clause filters in SQL?

问题描述

假设您有一个存储过程，并且它带有一个可选参数.您要在SQL查询中使用此可选参数.通常，这是我看到的结果:

Let's say you have a stored procedure, and it takes an optional parameter. You want to use this optional parameter in the SQL query. Typically this is how I've seen it done:

SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND (@MyOptionalParam IS NULL OR t1.MyField = @MyOptionalParam)

这似乎工作得很好，但是如果您在STATISTICS IO ON上运行查询，则会导致大量逻辑读取.我还尝试了以下变体:

This seems to work well, however it causes a high amount of logical reads if you run the query with STATISTICS IO ON. I've also tried the following variant:

SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND t1.MyField = CASE WHEN @MyOptionalParam IS NULL THEN t1.MyField ELSE @MyOptionalParam END

它产生相同数量的高读.如果我们将SQL转换为字符串，然后对其调用sp_ExecuteSQL，则读取结果几乎为零:

And it yields the same number of high reads. If we convert the SQL to a string, then call sp_ExecuteSQL on it, the reads are almost nil:

DECLARE @sql nvarchar(max)

SELECT @sql = 'SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = ''test'''

IF @MyOptionalParam IS NOT NULL
BEGIN
     SELECT @sql = @sql + ' AND t1.MyField = @MyOptionalParam '
END

EXECUTE sp_ExecuteSQL @sql, N'@MyOptionalParam', @MyOptionalParam

我疯了吗?为什么可选的where子句如此难于正确?

Am I crazy? Why are optional where clauses so hard to get right?

更新:我基本上是在问是否有一种方法可以将标准语法保留在存储过程中并获得低逻辑读取，如sp_ExecuteSql方法那样.构建一个字符串对我来说似乎完全疯狂……更不用说它使维护，调试和可视化变得更加困难.

Update: I'm basically asking if there's a way to keep the standard syntax inside of a stored procedure and get low logical reads, like the sp_ExecuteSql method does. It seems completely crazy to me to build up a string... not to mention it makes it harder to maintain, debug, visualize..

推荐答案

如果我们将SQL转换为字符串，然后对其调用sp_ExecuteSQL，则读取结果几乎为零...

If we convert the SQL to a string, then call sp_ExecuteSQL on it, the reads are almost nil...

1. 因为您的查询不再评估OR，如您所见，它杀死了可保存性
2. 使用sp_executesql时将缓存查询计划； SQL Server不必进行硬解析...

优质资源: The Curse&动态SQL的祝福

只要您使用参数化查询，就应该避免受到 SQL注入攻击.

As long as you are using parameterized queries, you should safe from SQL Injection attacks.

这篇关于处理“可选" SQL中的where子句过滤器的正确方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！