1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Rep Power

    Exluding lines within a WHERE clause in a SP


    I am passing several selection parameters to a stored procedure, and I would like to exclude particular lines within the where clause of the stored procedure if those parameters are set to a particular value.

    Below is a snippet (that doesn't work) from the stored procedure. Notice in particular the bits using the IN keyword. Instead of passing the entire list when the user selects the "all" checkbox, I would like to just pass the word "all" and not even mess with including the part of the WHERE clause that uses the IN keyword.

    I have a bad feeling that I cannot do this, and I will instead have to type in multiple where statements.

    WHERE a.signature = 1
    AND b.Date between @begindate and @enddate
    if classBillingcode != 'all'
    AND classBillingcode IN (@billingcodelist) --use for parameters
    if classBillingcode != 'all'
    AND tblTraineeTypecode IN (@traineetypelist)-- use for parameters
    "I'm between quotes right now"
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    you are testing classBillingcode in SP logic, but it appears that it is a table column


IMN logo majestic logo threadwatch logo seochat tools logo