[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Parameter driven conditional SQL query

Posted on 2010-09-20
3
Medium Priority
?
427 Views
Last Modified: 2012-05-10
I have an ASP.NET front end app that, at one point, generates Crystal Reports for client orders.
This reporting has just been extended to give the user the ability to filter the results.

Previously this data was filtered by date only. In crystal this query was something like:

select * from orders
where ordr_conf_d between '2010-09-01' and '2010-09-30'

The user will now need to see client orders only for a selected region.
By adding a region dropdown to this, the query will need to be updated to something like this:

select * from orders
where ordr_conf_d between '2010-09-01' and '2010-09-30'
and regn_i = 5

If no region is selected the region id will default to 0 and I would like to run the first query.
How do I achieve this? The following is not possible.

select * from orders
where ordr_conf_d between '2010-09-01' and '2010-09-30'
and regn_i IN (case when @RegionID > 0
                     then @RegionID
               else (select regn_i from lkup_regn)
               end)
0
Comment
Question by:dbasplus
3 Comments
 
LVL 11

Accepted Solution

by:
aelliso3 earned 2000 total points
ID: 33721841
In the code below, if @RegionID  = 0 then it will just skip the regn_i because of the OR statement in there
select * from orders
where ordr_conf_d between '2010-09-01' and '2010-09-30'
      and (regn_i = 5
           or @RegionID = 0)

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33721861
select * from orders
where ordr_conf_d between '2010-09-01' and '2010-09-30'
and (@RegionID = 0 or regn_id = @RegionID)
0
 

Author Closing Comment

by:dbasplus
ID: 33721864
Brilliant and simple, just what I needed.

This is the final result:

select * from orders
where ordr_crte_d between '2010-08-01' and '2010-08-31'
and (clnt_i = @ClientID or @ClientID = 0)
and (regn_i = @RegionID or @RegionID = 0)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question