Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: dynamic 'where' clause

    Hi

    I need some advice on which direction to take!

    Consider this statement:

    SELECT business_name FROM myTable WHERE town = @town AND county = @county

    My problem is that i will not always have the @county variable available. Is there a way to use an IF or a CASE inside the SQL statement (i know i can create two seperate sql statments but dont want to do it this way)? If it makes it easier, when the @county variable is not available, it has a value of 0.

    thanks again

    Ps, i also know how to do it using dynamic sql using the EXEC() command, but i'd prefer to steer clear of this method also.
    Last edited by mattock; 11-13-06 at 06:03.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT business_name 
    FROM myTable 
    WHERE (town = @town OR @town IS NULL)
    AND (county = @county OR @county = 0)
    ???
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Hi pootle

    If the @county has a value of 0 then i don't want to make it part of the WHERE clause, ie i dont want it to search the county column - in your example it would search all counties which are equal to 0?

    This is what i'm trying to acheive, but only use one SELECT statment:

    IF @county <> '0'
    BEGIN
    SELECT business_name FROM myTable WHERE town = @town AND county = @county
    END
    ELSE
    BEGIN
    SELECT business_name FROM myTable WHERE town = @town
    END

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Mattock

    Did you try the code? I know the answer is no It does just what you ask for.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    lol, i'm real sorry, got the monday morning blues! Thick mode was well and truly stuck to 'ON'.


    cheers again

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No probs
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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