I have a stored procedure that looks like:
我有一个存储过程,如下所示:
CREATE PROCEDURE dbo.usp_TestFilter
@AdditionalFilter BIT = 1
AS
SELECT *
FROM dbo.SomeTable T
WHERE
T.Column1 IS NOT NULL
AND CASE WHEN @AdditionalFilter = 1 THEN
T.Column2 IS NOT NULL
Needless to say, this doesn't work. How can I activate the additional where clause that checks for the @AdditionalFilter parameter? Thanks for any help.
不用说,这不起作用。如何激活检查@AdditionalFilter参数的附加where子句?谢谢你的帮助。
4 个解决方案
#1
6
CREATE PROCEDURE dbo.usp_TestFilter
@AdditionalFilter BIT = 1
AS
SELECT *
FROM dbo.SomeTable T
WHERE
T.Column1 IS NOT NULL
AND (@AdditionalFilter = 0 OR
T.Column2 IS NOT NULL)
If @AdditionalFilter is 0, the column won't be evaluated since it can't affect the outcome of the part between brackets. If it's anything other than 0, the column condition will be evaluated.
如果@AdditionalFilter为0,则不会对列进行求值,因为它不会影响括号之间部分的结果。如果它不是0,则将评估列条件。
#2
4
This practice tends to confuse the query optimizer. I've seen SQL Server 2000 build the execution plan exactly the opposite way round and use an index on Column1 when the flag was set and vice-versa. SQL Server 2005 seemed to at least get the execution plan right on first compilation, but you then have a new problem. The system caches compiled execution plans and tries to reuse them. If you first use the query one way, it will still execute the query that way even if the extra parameter changes, and different indexes would be more appropriate.
这种做法往往会混淆查询优化器。我已经看到SQL Server 2000以完全相反的方式构建执行计划,并在设置标志时使用Column1上的索引,反之亦然。 SQL Server 2005似乎至少在第一次编译时得到了执行计划,但是你遇到了一个新问题。系统缓存已编译的执行计划并尝试重用它们。如果您首先以一种方式使用查询,即使额外参数更改,它仍将以该方式执行查询,并且不同的索引更合适。
You can force a stored procedure to be recompiled on this execution by using WITH RECOMPILE in the EXEC statement, or every time by specifying WITH RECOMPILE on the CREATE PROCEDURE statement. There will be a penalty as SQL Server re-parses and optimizes the query each time.
您可以通过在EXEC语句中使用WITH RECOMPILE或每次在CREATE PROCEDURE语句中指定WITH RECOMPILE来强制在此执行时重新编译存储过程。每次SQL Server重新解析并优化查询时都会受到惩罚。
In general, if the form of your query is going to change, use dynamic SQL generation with parameters. SQL Server will also cache execution plans for parameterized queries and auto-parameterized queries (where it tries to deduce which arguments are parameters), and even regular queries, but it gives most weight to stored procedure execution plans, then parameterized, auto-parameterized and regular queries in that order. The higher the weight, the longer it can stay in RAM before the plan is discarded, if the server needs the memory for something else.
通常,如果要更改查询的形式,请使用带参数的动态SQL生成。 SQL Server还将缓存参数化查询和自动参数化查询的执行计划(其中它试图推断哪些参数是参数),甚至是常规查询,但它给予存储过程执行计划最大的权重,然后参数化,自动参数化和按顺序定期查询。如果服务器需要内存用于其他内容,则重量越高,在丢弃计划之前它在RAM中保留的时间越长。
#3
1
CREATE PROCEDURE dbo.usp_TestFilter
@AdditionalFilter BIT = 1
AS
SELECT *
FROM dbo.SomeTable T
WHERE
T.Column1 IS NOT NULL
AND (NOT @AdditionalFilter OR T.Column2 IS NOT NULL)
#4
0
select *
from SomeTable t
where t.Column1 is null
and (@AdditionalFilter = 0 or t.Column2 is not null)
#1
6
CREATE PROCEDURE dbo.usp_TestFilter
@AdditionalFilter BIT = 1
AS
SELECT *
FROM dbo.SomeTable T
WHERE
T.Column1 IS NOT NULL
AND (@AdditionalFilter = 0 OR
T.Column2 IS NOT NULL)
If @AdditionalFilter is 0, the column won't be evaluated since it can't affect the outcome of the part between brackets. If it's anything other than 0, the column condition will be evaluated.
如果@AdditionalFilter为0,则不会对列进行求值,因为它不会影响括号之间部分的结果。如果它不是0,则将评估列条件。
#2
4
This practice tends to confuse the query optimizer. I've seen SQL Server 2000 build the execution plan exactly the opposite way round and use an index on Column1 when the flag was set and vice-versa. SQL Server 2005 seemed to at least get the execution plan right on first compilation, but you then have a new problem. The system caches compiled execution plans and tries to reuse them. If you first use the query one way, it will still execute the query that way even if the extra parameter changes, and different indexes would be more appropriate.
这种做法往往会混淆查询优化器。我已经看到SQL Server 2000以完全相反的方式构建执行计划,并在设置标志时使用Column1上的索引,反之亦然。 SQL Server 2005似乎至少在第一次编译时得到了执行计划,但是你遇到了一个新问题。系统缓存已编译的执行计划并尝试重用它们。如果您首先以一种方式使用查询,即使额外参数更改,它仍将以该方式执行查询,并且不同的索引更合适。
You can force a stored procedure to be recompiled on this execution by using WITH RECOMPILE in the EXEC statement, or every time by specifying WITH RECOMPILE on the CREATE PROCEDURE statement. There will be a penalty as SQL Server re-parses and optimizes the query each time.
您可以通过在EXEC语句中使用WITH RECOMPILE或每次在CREATE PROCEDURE语句中指定WITH RECOMPILE来强制在此执行时重新编译存储过程。每次SQL Server重新解析并优化查询时都会受到惩罚。
In general, if the form of your query is going to change, use dynamic SQL generation with parameters. SQL Server will also cache execution plans for parameterized queries and auto-parameterized queries (where it tries to deduce which arguments are parameters), and even regular queries, but it gives most weight to stored procedure execution plans, then parameterized, auto-parameterized and regular queries in that order. The higher the weight, the longer it can stay in RAM before the plan is discarded, if the server needs the memory for something else.
通常,如果要更改查询的形式,请使用带参数的动态SQL生成。 SQL Server还将缓存参数化查询和自动参数化查询的执行计划(其中它试图推断哪些参数是参数),甚至是常规查询,但它给予存储过程执行计划最大的权重,然后参数化,自动参数化和按顺序定期查询。如果服务器需要内存用于其他内容,则重量越高,在丢弃计划之前它在RAM中保留的时间越长。
#3
1
CREATE PROCEDURE dbo.usp_TestFilter
@AdditionalFilter BIT = 1
AS
SELECT *
FROM dbo.SomeTable T
WHERE
T.Column1 IS NOT NULL
AND (NOT @AdditionalFilter OR T.Column2 IS NOT NULL)
#4
0
select *
from SomeTable t
where t.Column1 is null
and (@AdditionalFilter = 0 or t.Column2 is not null)