如果在存储过程中参数为null，则全选 [英] Select all if parameter is null in stored procedure

问题描述

我想在SQL Server中创建一个过程，该过程将选择并联接两个表.始终设置参数@ company，@ from和@to，但是@serie_type可以为NULL.如果@serie_type不为NULL，我只想包括指定的类型，简单的AND S.Type = @serie_type，但如果@serie_type为NULL，我想包括所有类型，简单的，只是不包括AND语句.我的问题是我不知道是否会设置@serie_type，因此我想拥有这样的东西:

/* pseudocode */
??? = AND (IF @serie_type IS NOT NULL S.Type = @serie_type)

这是程序的简化版:

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

不想重复选择，因为真正的选择要比这个大得多.

解决方案

无需执行 AND (@serie_type IS NULL OR S.Type = @serie_type)，因为SQL Server具有内置功能可以为您执行此逻辑.

尝试一下:

   .
   .
   AND  S.Type = isnull( @serie_type, S.Type)

这将返回

如果@serie_type为null，则为true；如果@serie_type不为null，则@serie_type =S.

从 MSDN :

IsNull用指定的替换值替换NULL.

ISNULL ( check_expression , replacement_value )

如果check_expression的值不为NULL，则返回该值； 否则，则隐式返回replacement_value 如果类型不同，则转换为check_expression的类型.

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:

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

解决方案

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.

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.

From the 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.

这篇关于如果在存储过程中参数为null，则全选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！