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

    Join Date
    Nov 2001
    Posts
    117
    Rep Power
    29

    Exluding lines within a WHERE clause in a SP


    Hello,

    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
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    you are testing classBillingcode in SP logic, but it appears that it is a table column

    rudy

IMN logo majestic logo threadwatch logo seochat tools logo