September 23, 2008 at 4:04 pm
Need a conditional WHERE clause. I’ll pass in 2 parameters - @pcustomer_id & @pcocustomer_id
If @pcocustomer_id > 0, I need:
SELECT [fields]
FROM incident_report
WHERE customer_id=@pcustomer_id
OR customer_id=@pcocustomer_id
If @pcocustomer_id = 0, then I only want:
SELECT [fields]
FROM incident_report
WHERE customer_id=@pcustomer_id
I came up with this, but I’m not sure if it’s right/functional/good code:
SELECT [fields]
FROM incident_report
WHERE customer_id=@pcustomer_id
OR (customer_id=@pcocustomer_id AND @pcocustomer_id>0)
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 23, 2008 at 4:12 pm
Looks good to me.
😎
September 24, 2008 at 6:25 am
I generally like to avoid using ORs in my WHERE clause so I modified your statement to use an AND. Check out the query plans for these two and see which works better in your environment.
declare @pcustomer_id int
, @pcocustomer_id int
select@pcustomer_id = 5
, @pcocustomer_id = 0
select*
fromincident_report
wherecustomer_id = @pcustomer_id
andcustomer_id = isnull(nullif(@pcocustomer_id, 0), customer_id)
select*
fromincident_report
wherecustomer_id = @pcustomer_id
orcustomer_id = @pcocustomer_id
_____________________________________________________________________
- Nate
September 24, 2008 at 8:29 am
RP_DBA (9/24/2008)
I generally like to avoid using ORs in my WHERE clause so I modified your statement to use an AND. Check out the query plans for these two and see which works better in your environment.
select*
fromincident_report
wherecustomer_id = @pcustomer_id
andcustomer_id = isnull(nullif(@pcocustomer_id, 0), customer_id)
I agree with avoiding OR's if possible, but I don't think the query I left in the code will meet the requirement as it appears the OP wants to return any records where the customer_id equals either of the parameters passed in. By using AND you would only get records if the 2 parameters passed in are the same.
Try out this test code:
[font="Courier New"]DECLARE @incident_report TABLE(customer_id INT)
DECLARE @pcustomer_id INT, @pcocustomer_id INT
SET @pcustomer_id = 1
SET @pcocustomer_id = 2
INSERT INTO @incident_report
SELECT
1
UNION ALL
SELECT
2
SELECT
*
FROM
@incident_report
WHERE
customer_id = @pcustomer_id AND
customer_id = ISNULL(NULLIF(@pcocustomer_id, 0), customer_id)
SELECT
*
FROM
@incident_report
WHERE
customer_id = @pcustomer_id OR
customer_id = @pcocustomer_id
SELECT
*
FROM
@incident_report
WHERE
customer_id=@pcustomer_id OR
(customer_id=@pcocustomer_id AND @pcocustomer_id>0)[/font]
Edit: To answer Art's original question, what you are doing is fine.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 24, 2008 at 8:59 am
Yep, you're right Jack. For some reason I went off under the assumption that one or the other would be populated but not both. Plus my query would've need to be further mod'd to isnull(nullif(@param, 0)) both arguments.
Sorry Art if I was leading you down the wrong path, wrote up during my first cup of coffee of the day. Should know better by now 😉
_____________________________________________________________________
- Nate
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply