假设您有一个存储过程，并且它带有一个可选参数.您要在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...
优质资源: The Curse&动态SQL的祝福
只要您使用参数化查询，就应该避免受到 SQL注入攻击.
As long as you are using parameterized queries, you should safe from SQL Injection attacks.
更多推荐
处理“可选" SQL中的where子句过滤器的正确方法?
发布评论