Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2012-02-24 : 15:48:34
|
| I'm doing an Orders report that gets run from a web page. The user has the option to include "cancelled" orders on this report if they so choose. So, I pass in a BIT parameter into my stored procedure, called "@IncludeCancelledOrders". My question is, what's the best way to filter my query to exclude orders with a status of "Cancelled", if the user sets @IncludeCancelledOrders = 1 (i.e. true)?Here's my table of all possible statuses for my orders:OrderStatusID | Name-----------------------------1 | New2 | Shipped3 | Closed4 | CancelledAnd, here's some samples of my order table:OrderId | OrderStatusID | OrderDate--------------------------------------------------222 | 1 | 2/1/2012333 | 1 | 2/10/2012444 | 3 | 2/13/2012555 | 4 | 2/20/2012So, in the above samples, if the user passes in @IncludeCancelledOrders with a value of 1 (true), order # "555", which is the only cancelled order, should be included. If they pass in a value of 0 (false), order # "555" should NOT be included.Any idea how I can do this?Thanks. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-24 : 15:57:20
|
You can add an additional condition to your WHERE clause like this:( @IncludeCancelledOrders = 1 OR OrderStatusID <> 4) |
 |
|
|
Goalie35
Yak Posting Veteran
81 Posts |
Posted - 2012-02-24 : 16:15:46
|
Thanks! That did it  |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-24 : 16:24:58
|
| When you use it, if performance seems to be a problem, you may need to rewrite it - take a look at this page: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ |
|
|
|
|
|
|
|
|
How to filter query based on specific criteria