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 |
|
snufse
Constraint Violating Yak Guru
469 Posts |
 Posted - 2011-05-11 : 14:29:31
|
I have following code snippet where I need to select either on date from / to range or bol# from /to rnage based upon user selection in parm parameters passed to the stored procedure.Is there a way to say "if @DateFrom/@DateTo <> nothing" then use date range as selection and "if @BolNumberFrom/@BolNumberTo <> nothing" then use bol# as selection range?where ((@DateFrom IS Null OR o.status_date >= @DateFrom) and (@DateTo IS Null OR o.end_load_time <= @DateTo)) or ((@BolNumberFrom IS Null OR o.movement_number >= @BolNumberFrom)) and (@BolNumberTo IS Null OR o.movement_number <= @BolNumberTo) and o.revision_number = 0 and oe.product_group_code <> 'ADD' and cu.customer_number <> 1000 and de.destination_number <> 9999 and (oe.gross_quantity > 0 or oe.net_quantity > 0) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-11 : 14:49:18
|
| Yes you can do this, but the simplest and most straightforward way that most of us would think of is also the worst in terms of performance. Take a look at these pages - they have examples as well as performance information:http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/http://www.sommarskog.se/dyn-search-2008.html |
 |
|
|
|
|
|
using "Where" clause based upon selection