How to ignore a parameter in stored procedure if i

问题:

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

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.

回答1:

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).

I personally use OPTION(RECOMPILE) in these queries. You use SQL Server 2008, so this option is a good choice. If you do go via the dynamic SQL route, make sure to read his another article The Curse and Blessings of Dynamic 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

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.

回答2:

Let's play with Dynamic - SQL:

CREATE PROCEDURE [dbo].[my_procedure]
     @var1 VARCHAR(100) = NULL,
     @var2 VARCHAR(100) = NULL,
     @var3 VARCHAR(100) = NULL,
     @var4 VARCHAR(100) = NULL,
     /* ........  */ 
     @var10 VARCHAR(100) = NULL,
     @debug INT         = 0
AS
BEGIN
SET NOCOUNT ON;

DECLARE 
    @sql        NVARCHAR(MAX),                                
    @paramlist  NVARCHAR(4000),                              
    @nl         CHAR(2) = CHAR(13) + CHAR(10);


/* Main query here */
SELECT @sql = 
   '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
    /* ........ and so on */
    INNER JOIN tab10 AS j
      ON J.id = i.id
    WHERE 1 = 1 ' + @nl;

IF @var1 IS NOT NULL                                            
    SELECT @sql += ' AND a.id = @var1' + @nl;  

IF @var2 IS NOT NULL                                            
    SELECT @sql += ' AND b.id = @var2' + @nl;  

/* ... */

IF @var10 IS NOT NULL                                            
    SELECT @sql += ' AND j.id = @var10' + @nl;

/* If sorting needed just add it;
SELECT @sql += ORDER BY a.id;
*/

IF @debug = 1
    PRINT @sql;


SELECT @paramlist = 
      '@var1     VARCHAR(100),   
       @var2     VARCHAR(100),
       /* ... */
       @var10     VARCHAR(100)';

/*CREATE TABLE #temp definition here */

INSERT INTO #temp(col1, col2, ...)
EXEC [dbo].[sp_executesql]
           @sql,
           @paramlist,
           @var1,
           @var2,
           /* ... */
           @var10;

IF @@ROWCOUNT < 10 
THEN
    SELECT *       /* Use column names */
    FROM #temp;
ELSE
    PRINT 'Cannot display the records as count is more than 10';
END

Usage as any normal stored procedure + debug if want to see query:

 EXEC [dbo].[my_procedure]
            @var1 = 'AAA'
           ,@var2 = 'BBB'
           ,@debug = 1;

• Formatting is everything, especially when Dynamic-SQL is used.
• Instead of SELECT COUNT(*) FROM #temp use @@ROWCOUNT;
• Printing from SP is not good practice, better to return some value to indicate it in OUT parameter.
• If you don't like WHERE 1 = 1 you can first check if all arguments are NULLs and skip it.
• You don't need to use Dynamic SQL at all, just use IF THEN ELSE IF THEN ELSE IF ... ELSE and do massive duplication of hard to maintain code (if Line Of Code is metric used to measure your productivity it can be useful)

回答3:

You just need to give the parameters which are being used.

If only value for Var1 needs to be passed.

proc1 @Var1 = Var1Value

If the value for Var6 needs to be passed.

Proc1 @Var6 = Var6Value

Please use case in this like

 where case @var1 when null then 1 else  a.id end =  case @var1 when null then 1 else  @var1 end 

Hope this will help you Thanks