I inherited some ugly nested case statements. I would like to see if there are any alternatives so they are more readable and maintainable.
Here is some simplified code
create table #tblValues (val1 varchar(20), val2 int, val3 int, val4 datetime, val5 datetime, val6 int, val7 int)
insert into #tblValues
select null, 1, 150, '01/11/2010', '09/25/2010', 50, 1 union all
select 'Y', 1, 150, '01/11/2010', '09/25/2010', 50, 1 union all
select null, 0, 150, '01/12/2010', '09/25/2010', 50, 1 union all
select null, 1, 150, '01/12/2010', '01/13/2010', 60, 1
Then the SELECT
Case When Isnull(val1,'N') = 'N' Then
Case When val2 = 1 Then
Case When val3 is not null Then
CASE WHEN (val4 IS NOT NULL) AND (val5 IS NOT NULL) THEN
CASE WHEN val4 < '01/12/2010' THEN
CASE WHEN DATEDIFF(D, val4, val5)>=65 THEN 10
CASE WHEN val6 >= 120 THEN 9 -- days < 65 & minutes are greater than 120 so they get 1/2 price
ELSE 11 -- mintues less than 120
ELSE -- val4 >= '01/12/2010' --Version 1.4 New rules apply
CASE WHEN DATEDIFF(D, val4, val5)>=7 THEN 10 --Version 1.4
CASE WHEN val6 >= 60 THEN 9 -- days < 7 & minutes are greater than 60 so they get 1/2 price
ELSE 11 -- mintues less than 60
END --Version 1.4
ELSE -- val4 IS NULL AND val5 IS NOT NULL --Version 1.1
Case When val7 in (0,1,5) Then
Case When val6 >= 120 Then 4 -- minutes are greater than 120
Else 3 -- mintues less than 120
Else Case When val7 in (2,3) Then 5
Else Case When val7 in (4) Then 6
Else 7 -- invalid Code
END -- val4 IS NULL AND val5 IS NOT NULL --Version 1.1
Else 8 -- unit price record not found
Else 2 -- Not a phone
Else 1 -- previously issued an check
End As RuleResult, *
My eyes hurt just looking at this.
I have looked into coalesce but I am not sure it can handle something so complex, I'd be handling nested coalesce statements instead of nested case statements.
I also read the articles in this post about rule engines
But I don't understand the articles fully and they seem overly simplistic to me, just tables holding values and the operators are still defined in the code. I on the other hand, have ISNULL, IS NOT NULL, IN, and DateDiff to evaulate.
I thought about:
1) If I store just the values and not the operators into a table then then end results might not be much better than what I have now.
2) If I push the operators/conditions in a table then I can only see doing some complex dynamic SQL to evaluate.
Any thoughts? Thanks.