Results 1 to 10 of 10
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: If(something) then include in WHERE clause

    Hi Would it be possible to do the following in TSQL stored procedure.

    SELECT
    STUDENTID,
    STUDENTNAME
    FROM
    STUDENTS
    WHERE
    STUDENTAGE = @STUDENTAGE
    IF(STUDENTSEX<>null)
    BEGIN
    AND STUDENTSEX = @STUDENTSEX
    END
    So if the "StudentSex" is null then leave it out of the where clause.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Code:
    select
    studentid
    , studentname
    from
    students
    where
    studentage = @studentage
    and
    (studentsex is null
    or
    studentsex = @studentsex)
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2008
    Posts
    97
    Thanks but I meant to say if @studentsex (the variable) is not null then use the additional filter.

    I think this changes because you are using 'studentsex' the column it seems.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Right, that's rather different! :P
    Code:
    IF @StudentSex IS NULL
    BEGIN
    SELECT
     StudentID
    , StudentName
    FROM
     Students
    WHERE
     StudentAge = @StudentAge
    END
    ELSE
    BEGIN
    SELECT
     StudentID
    ,  StudentName
    FROM
     Students
    WHERE
     StudentAge = @StudentAge
    AND
     StudentSex = @StudentSex
    END
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    May 2008
    Posts
    97
    Thanks I appreciate the quick reply but is there a less cumbersome way of doing this? Lets say I have 5 different filters and any of these filters may or may not be applied.

    Background info on the app: My web service is calling this stored procedure based on a few parameters that come from a form so the user may or may not include any combination of 5 'WHERE' filters.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    If you'd clearly stated the problem in the first place, you could have saved you and me a lot of time.

    The easiest way is to dynamically build an SQL statement in your front end and pass that to the SQL database for processing. That way, you only pick up the fields that matter. If you absolutely must do this within the back end, you could try building up a SQL statement in a variable within the stored procedure. Then you build up the WHERE clause according to the null state of the passed variables, and then execute the generated statement. If you go this route, start your WHERE clause with "WHERE 1 = 1 ", and then build all the others with " AND" at the start. That avoids any tedious programming to find out which is the first variable to be populated.
    NB
    I've never tried the latter approach, but I've seen it illustrated in this forum, so try searching for dynamic SQL and see what you get.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    All three of these should work:
    Code:
    select	studentid,
    	studentname
    from	students
    where	studentage = @studentage
    	and
    		(@studentsex is not null
    		and
    		studentsex = @studentsex)
    			
    select	studentid,
    	studentname
    from	students
    where	studentage = @studentage
    	and	(studentsex = @studentsex
    		or @studentsex is null)
    
    select	studentid,
    	studentname
    from	students
    where	studentage = @studentage
    	and coalesce(@StudentSex, StudentSex) = StudentSex
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    May 2008
    Posts
    97
    Thanks a lot! After a lot of pain I gave up yesterday. I plan to be back on the database side of things in the next week and I will give your tip a go.

    But for me its now back on web services and the flex client.. last time I volunteer to do everything in 2 months.

  9. #9
    Join Date
    May 2008
    Posts
    97
    Your second example (shown below) has been working great for me. Unfortunately, I do not understand why it works. Any help would be appreciated. Any logically explanation in plain english would be great.

    Code:
    select	studentid,
    	studentname
    from	students
    where	studentage = @studentage
    	and	(studentsex = @studentsex
    		or @studentsex is null)

  10. #10
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Maybe i'm confused.

    But this should select columns where the
    studentage is equal to the @studentage AND
    the (@studentsex is equal to the studentsex OR null).

Posting Permissions

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