如果存储过程中参数为空，则选择全部

发布于 2024-12-05 16:20:16 字数 785 浏览 0 评论 0原文

我想在 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

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

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉世弥音 2024-12-12 16:20:16

常见的做法是：

WHERE 
C.Id = @company          
AND S.Created >= @from         
AND S.Created <= @to 
AND  (@serie_type IS NULL OR S.Type = @serie_type)

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)

回复收藏 0 原文

挽袖吟 2024-12-12 16:20:16

无需执行 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.Type 的结果。

来自 MSDN：

IsNull 用指定的替换值替换 NULL。
ISNULL（检查表达式，替换值）
check_expression的值不为NULL则返回；
否则，replacement_value 隐式返回后返回
如果类型不同，则转换为 check_expression 的类型。

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.

回复收藏 0 原文

说好的呢 2024-12-12 16:20:16

您还可以在 where 子句中使用 case 语句

where e.ZoneId = case when @zoneid=0 then e.zoneid else @zoneid end

回复收藏 0 原文

梨涡 2024-12-12 16:20:16

非常干净的方法是将您的 @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

The very clean approach will be define your @serie_type to be 0. Take a look below:

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

回复收藏 0 原文

~没有更多了~