在存储过程中选择所有if参数为空

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可以为空。如果@serie_type不是NULL，我只想包含指定的类型，simple和S。Type = @serie_type，但是如果@serie_type是NULL，我想包含所有类型，很简单，就是不包含AND语句。我的问题是我不知道@serie_type是否会被设置，所以我想要o有这样的东西:

/* 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.

不要复制select，因为真正的select要比这个大得多。

4 个解决方案

#1

There is no need to do AND (@serie_type IS NULL OR S.Type = @serie_type) as SQL Server has a built in function to do this logic for you.

不需要这样做(@serie_type是NULL或S。类型= @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是null或@serie_type = S的结果，则为true。如果@serie_type不是null，则键入。

From the MSDN:

从MSDN:

IsNull Replaces NULL with the specified replacement value.

IsNull用指定的替换值替换NULL。
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.

如果check_expression值不是NULL，则返回;否则，若类型不同，则在将replacement_value隐式转换为check_expression类型之后返回。

#2

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)

#3

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

在e。ZoneId = @zoneid=0，则e。其他zoneid @zoneid结束

#4

The very clean approach will be define your @serie_type to 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

#1