Friday, June 22, 2007

SQL Server: "All Values" parameters in the WHERE clause

Recently, I put a parameter in a WHERE clause in a stored procedure so my client could select values from a list in SQL Reporting Services:

projectIDprojectTypeIDprojectManagerIDprojectName
1100200TPS Reports
2100201Interest rounding exploit
3101200FLARE evaluation
4101201B.E. Channel Referral

select *
from projects
where projectTypeID = @projectTypeID

The client liked it a lot, but then he wanted to add an "All Project Types" option to the list. When the "All Project Types" option was selected, the parameter would be set to -1, and the WHERE clause would essentially go away.

I initially solved this with a temporary table and an IF clause, but found that SQL Reporting Services is squirrely about temporary tables. So I found this sweet little solution:

select * from projects
where @projectTypeID = -1
or projectTypeID = @projectTypeID

Which is really quite smart, isn't it? When the parameter is -1, the first part of the WHERE clause is TRUE, and TRUE OR anything is TRUE. When the WHERE clause evalutuates to TRUE always, then all rows are returned. If @projectTypeID is not -1, for example 100, then the first part of the WHERE clause is FALSE, but FALSE ORed with TRUE is still TRUE. So in our example of @projectTypeID = 100, two rows will be returned.

There is one last wiggle. My client wanted to be able to specify "All Project Types" and "All Project Managers". The same logic works, but you have to write the WHERE clause correctly:

select * from projects
where (@projectTypeID = -1 or projectTypeID = @projectTypeID)
and (@projectManagerID = -1 or projectManagerID = @projectManagerID)

See if you can trick out the logic your self on this.

Special thanks to Ilo at Ilo's Quest for this trick.

9 comments:

  1. Thank you! it worked great! Thought I was getting an error because I used string values instead of INT values in my table, so it did not like the "-1"...

    As a comment for others: if your parameter/column values use a string instead of an int, you can modify the SQL statment like this:

    SELECT * FROM Table.Events
    WHERE(@Event_Type= 'xax' or Event_Type = @Event_Type)

    Which lets you select all values from the query once you set the default value of your dropdown to "xax"

    Thanks again!

    ReplyDelete
  2. it helps me a lot. I searched it for where all condition and it stops at your article. thank's

    ReplyDelete
  3. Marry me!!!
    :))
    No, really, thanks for the article. very helpful!

    ReplyDelete
    Replies
    1. You're welcome! I just saw your comment today; sorry for the delay! Glad it was helpful.

      Delete
    2. ይመችህ አቦ! በጣም ይረዳል፡፡ አመሰግናለሁ፡፡

      Delete
  4. I was using iReport and i've changed this way my parameter:
    "..WHERE $P{carType}=-1 or idCarType=$P{carType}"
    and it worked excelent, for all types and for an specific type. I thank you a lot!

    ReplyDelete

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.