如果参数值为空，如何忽略存储过程中的参数 [英] How to ignore a parameter in stored procedure if its value is null

问题描述

我有一个存储过程，我必须在其中连接 10 个表并使用 WHERE 条件根据存储过程中传递的参数过滤记录.例如:

I have a stored procedure in which I have to join 10 tables and use WHERE condition to filter the records based on the parameters passed in the stored procedure. For example:

create procedure proc1
    @var1 varchar(100) = null,
    @var2 varchar(100) = null,
    @var3 varchar(100) = null,
    @var4 varchar(100) = null,
    ........   
    @var10 varchar(100) = null
as
begin
    insert into #a
    select * from
    (
        select * from 
            tab1 as a
            inner join tab2 as b on a.rollnumber = b.rollnumber
            inner join tab3 as c on c.city = b.city
            ........
            inner join tab10 as j on J.id = i.id
        where 
            a.id = isnull(@var1,a.id) and 
            b.id = isnull(@var2,b.id) and 
            c.id = isnull(@var3,c.id) and 
            ...........
            J.id = isnull(@var10,j.id)
    ) as abc

    if (select count(*) from #a) < 10 
    begin
        select * from #a
    end
    else 
    begin
        print 'Cannot display the records as count is more than 10'
    end
end

上述存储过程工作正常，但速度很慢，因为 WHERE 子句中有 10 个条件.如果某些参数未提供给存储过程，我想要的是跳过条件.例如，如果只有 3 个参数传递给存储过程，则 WHERE 子句应该跳过 WHERE 子句中的其余参数.这将使程序更有效率.所以，如果 @var1 没有被传递，a.id 的所有值都应该被返回.

Above stored procedure is working fine, but it is slow because there are 10 conditions in the WHERE clause. What I want is to skip the condition if some parameters are not supplied to the stored procedure. For example, if only 3 parameters are passed to the stored procedure, then WHERE clause should skip the rest of the parameters from the WHERE clause. This will make the procedure more efficient. So, if @var1 is not passed, all the values of a.id should be returned.

推荐答案

有一篇很不错的文章T-SQL 中的动态搜索条件 作者:Erland Sommarskog.他解释了几种可以使用的方法，并比较了@lad2025 建议的构建动态 SQL 和使用 OPTION(RECOMPILE) 的情况.

There is a very good article Dynamic Search Conditions in T‑SQL by Erland Sommarskog. He explains several approaches that could be used and compares building dynamic SQL as @lad2025 suggested and using OPTION(RECOMPILE).

我个人使用 OPTION(RECOMPILE) 在这些查询中.您使用的是 SQL Server 2008，因此此选项是一个不错的选择.如果您确实通过动态 SQL 路线，请务必阅读他的另一篇文章动态 SQL 的诅咒和祝福.

所以，你的程序变成了这样:

So, your procedure becomes something like this:

create procedure proc1
    @var1 varchar(100) = null,
    @var2 varchar(100) = null,
    @var3 varchar(100) = null,
    @var4 varchar(100) = null,
    ........   
    @var10 varchar(100) = null
as
begin
    insert into #a
    select * from
    (
        select * 
        from
            tab1 as a
            inner join tab2 as b on a.rollnumber = b.rollnumber
            inner join tab3 as c on c.city = b.city
            ........
            inner join tab10 as j on J.id = i.id
        where 
            (a.id = @var1 OR @var1 IS NULL)
            and (b.id = @var2 OR @var2 IS NULL)
            and (c.id = @var3 OR @var3 IS NULL)
            ...........
            and (J.id = @var10 OR @var10 IS NULL)
    ) as abc
    OPTION(RECOMPILE);

    if (select count(*) from #a) < 10 
    begin
        select * from #a
    end
    else 
    begin
        print 'Cannot display the records as count is more than 10'
    end
end

顺便说一下，通过检查 count() 并不清楚您想要实现什么，但也许您需要的只是简单的 TOP(10)最多返回 10 个第一行.如果您确实使用 TOP 来一致地返回结果，请确保添加 ORDER BY 子句.如果您不知道，您可以在您的过程中使用另一个参数来指示要返回的最大行数，并在 TOP(@ParamMaxRowCount) 中使用它.有时会返回结果集而有时只打印一条消息的存储过程并不常见.

By the way, it is not clear what you are trying to achieve by checking the count(), but maybe all you need is simple TOP(10) to return at most 10 first rows. Make sure to add ORDER BY clause if you do use TOP to return results consistently. If you didn't know, you can have another parameter of your procedure to indicate the maximum number of rows to return and use it in TOP(@ParamMaxRowCount). It is not common to have a stored procedure that sometimes returns result set and sometimes only prints a message.

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