Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Question Unanswered: Help building conditional WHERE clause from variables

    Hi,

    I need help in building a conditional WHERE clause using variables passed to a stored procedure. My requirements are to build a search page and to use a stored procedure instead of constructing my SQL based upon my search criteria.

    So, if they enter a value, I need to filter on that value - otherwise, I need to ignore it so that I can return all results. I can't figure out a way to do this with CASE statements, and I'm really at a loss. Any help you guys could provide would be greatly appreciated.

    Thanks!

    My psuedo-SQL:

    Code:
    CREATE PROCEDURE [sp_mySearch]
         @category_id int = 0
    AS
    BEGIN
    
         SELECT * FROM tblMarketplace 
         WHERE Active=1
    
         IF @category_id > 0 THEN
               AND CategoryID=@category_id
         END
    
         ORDER BY Title
    END

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    CREATE PROCEDURE [sp_mySearch]
    @category_id int = 0
    AS
    BEGIN

    SELECT * FROM tblMarketplace
    WHERE Active=1

    IF @category_id > 0 THEN
    AND CategoryID=@category_id
    END

    ORDER BY Title
    END

    this will be a rough try as i am having my dinner along with typing.

    create procedure sp_mysearch
    @category_id int
    as
    begin
    if @category_id is null
    begin
    select * from tblmarketplace
    where active=1
    end
    else
    select * from tblmarketplace
    where active=1
    and categoryid=@category_id
    end

    soem thing like that .

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    IF branching your code in versions of sql server before 2005 can cause issues with your query plan caching. Also, please never use SELECT * in production code.

    CREATE PROCEDURE [sp_mySearch]
    @category_id int = 0
    AS
    SELECT [always_use_explicit_column_list]
    FROM tblMarketplace
    WHERE Active=1
    AND (@category_id <= 0
    OR CategoryID=@category_id)
    ORDER BY Title
    GO
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Quote Originally Posted by mishaalsy View Post
    this will be a rough try as i am having my dinner along with typing.

    create procedure sp_mysearch
    @category_id int
    as
    begin
    if @category_id is null
    begin
    select * from tblmarketplace
    where active=1
    end
    else
    select * from tblmarketplace
    where active=1
    and categoryid=@category_id
    end

    soem thing like that .
    I'd like to use that, but I'm actually passing in several variables (I just stripped it down to make my post as streamlined as possible).

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You essentially have multiple optional parameters.

    The balance is then between:

    • Efficient querying
    • Efficient coding (i.e. not writing billions of lines)
    • Safe coding (i.e. not vulnerable to SQL Injection)

    Dynamic Search Conditions in T-SQL (SQL 2005 and earlier)
    Personally my preference is for parameterised dynamic SQL using sp_executesql.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    my approach can work with multiples.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    But it will result in scans.
    Dynamic Search Conditions in T-SQL (SQL 2005 and earlier)
    That's a link to the specific section about that technique BTW.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    your guy appears more open minded and reasonable than you are. dynamic sql is just a pain to debug and maintain. if you are dealing with 1000s of procs and you need to find dependencies in you code base, dynamic sql is is going to frustrate the crap out of you. I did not say it is always the solution, did I?

    So if this method always yields a table scan, it is worthless and should never be used? Not really so. Sometimes you can reasonably assume that the amount of data that you are to search will not exceed, say, 10.000 rows. As long as the response times are acceptable for the users, and there are no locking issues, there is no reason to make your programming more complex than necessary. And there are plenty of cases where you need to add an extra optional filter to an already efficient query on a non-indexed column, a case where this method is an excellent choice. I would go as far as to say that this technique should be a staple in your T-SQL programming. You just need to know when it's not the right thing to use.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus View Post
    I did not say it is always the solution, did I?
    No, but you didn't say it had drawbacks either. And nor did I about sp_dynamicsql so you were right to mention that.

    The point is that there is no pre-2008 method that does not have a drawback of some sort which is why I said that this sort thing is a balancing act.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    so I am being admonished for omissions, but the question was not about performance. whatever. you should be signing off by now anywho.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    But why when we are having so much fun?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah why don't you come over. we will do the town. I just realized I spent 2 days optimizing some procs that have been deactivated in production thanks to some stale perf stat data someone sent me, so I can use a drink. Now when the bosses come in next week I can say I did nothing this week and I can ask about the plan for my transition because I so deserve it.

    I am going for a walk.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •