SQL 存储过程中参数为空时全选
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7513955/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select all if parameter is null in stored procedure
提问by Andreas
I want to create a procedure in SQL Server that will select and join two tables. The parameters @company, @from and @to are always set but @serie_type can be NULL. If @serie_type is not NULL i just want to include the specified types, simple AND S.Type = @serie_type, but if @serie_type is NULL i want to include all types, simple, just dont include the AND statement. My problem is that i dont know if @serie_type will be set therefore i would like o have something like this:
我想在 SQL Server 中创建一个过程来选择并连接两个表。参数@company、@from 和@to 始终设置，但@serie_type 可以为NULL。如果@serie_type 不是NULL，我只想包括指定的类型， simple AND S.Type = @serie_type，但如果@serie_type 是NULL 我想包括所有类型，简单，只是不包括AND 语句。我的问题是我不知道是否会设置@serie_type，因此我希望有这样的东西：
/* pseudocode */
??? = AND (IF @serie_type IS NOT NULL S.Type = @serie_type)
Here is a simpifyed version of procedure:
这是程序的简化版本：
CREATE PROCEDURE Report_CompanySerie
@company INT,
@serie_type INT,
@from DATE,
@to DATE
AS
BEGIN
SELECT
*
FROM Company C
JOIN Series S ON S.Company_FK = C.Id
WHERE C.Id = @company
AND S.Created >= @from
AND S.Created <= @to
/* HERE IS MY PROBLEM */
AND ???
END
GO
Don't want to duplicate the select becaust the real select is way bigger then this.
不想复制选择，因为真正的选择比这大得多。
回答by sll
The common approach is:
常见的做法是：
WHERE
C.Id = @company
AND S.Created >= @from
AND S.Created <= @to
AND (@serie_type IS NULL OR S.Type = @serie_type)
回答by Preet Sangha
There is no need to doAND (@serie_type IS NULL OR S.Type = @serie_type)as SQL Server has a built in function to do this logic for you.
有没有必要做AND (@serie_type IS NULL OR S.Type = @serie_type)与SQL Server有一个内置的功能，为你做这个逻辑。
Try this:
尝试这个：
.
.
AND S.Type = isnull( @serie_type, S.Type)
This returns
这返回
true if @serie_type is null or the result of @serie_type = S.Type if @serie_type is not null.
如果@serie_type 为空或@serie_type = S.Type 如果@serie_type 不为空，则为真。
From the MSDN:
从MSDN：
IsNull Replaces NULL with the specified replacement value.
ISNULL ( check_expression , replacement_value )The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.
IsNull 用指定的替换值替换 NULL。
ISNULL ( check_expression , replacement_value )如果不为NULL，则返回check_expression 的值；否则，如果类型不同，则将replacement_value隐式转换为check_expression的类型后返回。
回答by Shreyas Maratha
You can also use case statement in the where clause
您还可以在 where 子句中使用 case 语句
where e.ZoneId = case when @zoneid=0 then e.zoneid else @zoneid end
where e.ZoneId = case when @zoneid=0 then e.zoneid else @zoneid end
回答by SuicideSheep
The very clean approach will be define your @serie_typeto be 0. Take a look below:
在很干净的做法将被定义@serie_type为0。看看下面：
CREATE PROCEDURE Report_CompanySerie
@company INT,
@serie_type INT = 0,
@from DATE,
@to DATE
AS
BEGIN
SELECT
*
FROM Company C
JOIN Series S ON S.Company_FK = C.Id
WHERE C.Id = @company
AND S.Created >= @from
AND S.Created <= @to
/* HERE IS MY PROBLEM */
AND (@serie_type = 0 OR S.SerieType = @serie_type)
END
GO
