March 17, 2014 at 2:24 pm
Hi Trying to use an if in a where clause using a parameter value
My where looks like ..
WHERE
(CostCenter.Code = '918')
AND ((CostCtrGrp.AdmitDate <= @EndDate) AND (CostCtrGrp.DischargeDate IS NULL) AND (Unit.Code > '899')) OR
((CostCenter.Code = '918') AND (CostCtrGrp.AdmitDate <= @EndDate) AND (CostCtrGrp.DischargeDate >= @StartDate) AND (Unit.Code > '899'))
I have a parameter @Unit
I need to encompass it that
if @Unit = 'All' then where clause above
else
the where clause above with Unit.code = @Unit instead of (Unit.Code > '899')
Thanks
Joe
March 17, 2014 at 2:47 pm
Sorry, I posted the wrong thing...
March 17, 2014 at 2:56 pm
I would look into dynamic SQL and string manipulation. Be sure to examine the execution plans accordingly to ensure proper indexing.
[font="Arial"]Matthew[/font]
March 17, 2014 at 2:57 pm
jbalbo (3/17/2014)
Hi Trying to use an if in a where clause using a parameter valueMy where looks like ..
WHERE
(CostCenter.Code = '918')
AND ((CostCtrGrp.AdmitDate <= @EndDate) AND (CostCtrGrp.DischargeDate IS NULL) AND (Unit.Code > '899')) OR
((CostCenter.Code = '918') AND (CostCtrGrp.AdmitDate <= @EndDate) AND (CostCtrGrp.DischargeDate >= @StartDate) AND (Unit.Code > '899'))
I have a parameter @Unit
I need to encompass it that
if @Unit = 'All' then where clause above
else
the where clause above with Unit.code = @Unit instead of (Unit.Code > '899')
Thanks
Joe
The IF statement is not a logical operator, it controls flow. As a result you can't have an IF in a where clause.
It seems that you are trying to over complicate this.
See if this works.
WHERE
(CostCenter.Code = '918')
AND ((CostCtrGrp.AdmitDate <= @EndDate) AND (CostCtrGrp.DischargeDate IS NULL) AND (Unit.Code > '899' OR @Unit = 'All' ) ) OR
((CostCenter.Code = '918') AND (CostCtrGrp.AdmitDate <= @EndDate) AND (CostCtrGrp.DischargeDate >= @StartDate) AND (Unit.Code > '899'))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2014 at 2:58 pm
It seems like you are getting dangerously close to a "catch all" query. You should read up on that topic here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2014 at 3:18 pm
Hi
I think this does want you want. I've also rearranged it slightly, so I hope I haven't misinterpreted your requirements
WHERE
(CostCenter.Code = '918') AND
(CostCtrGrp.AdmitDate <= @EndDate) AND
(
(Unit.Code > '899' and @unit = 'All') OR
(Unit.Code = @unit)
) AND
(
(CostCtrGrp.DischargeDate IS NULL) OR
(CostCtrGrp.DischargeDate >= @StartDate)
)
you could probably use a case statement, but I suspect the performance wouldn't be great
...
CASE WHEN Unit.Code = @unit THEN 1
WHEN Unit.Code > '899' and @unit = 'All' THEN 1
ELSE 0
END = 1
...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply