locked
SP with many parameters with NULL RRS feed

  • Question

  • Hello!

    I would like to pass many parameters to Stored Procedure, but any can be NULL that means the user does not want to use that parameter to filter the query.

    I USE: WHERE (row1=@Parameter1 OR @Parameter1 IS NULL) ...

    In case @Parameter is NOT NULL then left side of the query is executed, in case @Parameter is NULL then right side gives "1" and "OR 1" means @Parameter is ignored.

    But in case I have 10 parameters each with NULL value then Stored Procedures must executes 1 OR 1 OR 1 OR 1 OR 1 OR 1 OR 1...... which cause slow.

    How could I handle NULL parameters(which means no need to filter with that parameter) in Stored Procedures?

    Thank you!

    Thursday, October 14, 2010 8:28 AM

Answers

All replies

  • what kind of parameters they are?? If they are integers we can use some technique like below.

    where x.record_Id >= isnull(@TheID, convert(int, 0x80000000)) 
     and x.record_Id <= isnull(@TheID,convert(int, 0x7fffffff))
    
    

    see this thread about the above logic

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/2a4d2538-524d-429d-a793-2c48ac763490/

    If they are strings, you can use dynamic sql. But dynamic sql will cause security problems.. you must Read this if u wants to use dynamic sql..

    http://www.sommarskog.se/dynamic_sql.html

    • Proposed as answer by Naomi N Thursday, October 14, 2010 2:25 PM
    • Marked as answer by Kalman Toth Tuesday, October 19, 2010 12:11 PM
    Thursday, October 14, 2010 8:42 AM
  • You are using  fine but not efficient query in terms of performance. See Erland's article posted by ramireddy
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 14, 2010 8:52 AM
    Answerer
  • Try like this;

    WHERE (row1 = IsNull(@Parameter1,row1))
    --Or
    WHERE (row1 = case when @Parameter1 is Null then row1 else @Parameter1 end
    

    Please visit my Blog for some easy and often used t-sql scripts
    Thursday, October 14, 2010 9:36 AM
  • For this kind of situation you can write the dynamic query

    Suppose you have 3 parameter but you do not know when the value will come or null that case

    build the query dynamicaly so that each part you can add it or exclude it

    DECLARE

     

    @Query varchar(max)

    DECLARE

     

    @WhereClause varchar(max)

    SET

     

    @whereclause = ''

    if

     

    @parmeter1 is not null

    begin

    SET

     

    @whereclause = 'col1 = '''+ @parmeter1 + ''''

    end

    if

     

    @parmeter2 is not null and @whereclause <> ''

    begin

    SET

     

    @whereclause = @whereclause + 'col2 = ' ''+ @parmeter2 + ''''

    end

    else

    begin

    SET

     

    @whereclause = 'col2 = ' ''+ @parmeter2 + ''''

    end

    if

     

    @parmeter3 is not null and @whereclause <> ''

    begin

    SET

     

    @whereclause = @whereclause + 'col3 = ' ''+ @parmeter3 + ''''

    end

    else

    begin

    SET

     

    @whereclause = 'col3 = ' ''+ @parmeter3 + ''''

    end

    SET

     

    @query = 'SELECT * FROM tabl1 WHERE ' + @whereclause

    EXEC

     

    (@query)

    OR for increase the perormance you can use the sp_execute procedure ..


    " Education is the beginning of transformation. Dedicate yourself to daily learning via Blogs/Forums/books and coaching "
    Click here to read my blog
    Thursday, October 14, 2010 9:56 AM
  • Hi jaczol,

    May be it will help you if tune your query in this way,

    Where (@Parameter1 Is Null or row1 = @Parameter1)......

    In this query it will check the second condition when @Parameter1 contains some value. If @Parameter1 is null it won't check the second condition. But where your query i guess if @Parameter1 value is null, it checks both conditions because you make the condition @Parameter1 Is Null after OR.

    I hope this link will help you.

    http://www.sqlservercentral.com/articles/conditional/70889/ 

    Thanks

    Pavan

    Thursday, October 14, 2010 10:03 AM
  • There are several options here, and what to use depends on various options, among them is your version and build of SQL Server. The article to read is http://www.sommarskog.se/dyn-search.html
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, October 14, 2010 10:40 AM