Case expressions may only be nested to level 10
You don't need to nest the case statements, you can have many WHEN ... THEN
CASE
WHEN @x = 1 THEN 1
WHEN @x = 2 THEN 2
WHEN @x = 3 THEN 3
ELSE 4
END
I had written 12 nested IIF statements for a view which errored as a Nested Case Error. I was taking 12 columns (Jan - Dec) which held numeric values. The view was to convert each month to a row. I then realized I could split the nested IIFs into two groups of six, and add them together! It worked!
select AP.Year, AP.Period, RPA.Company, RPA.Contract, RPA.Description, RPA.PM, RPA.ProjectManager,
IIF(AP.Period=1, RPA.JanNetBilled, IIF(AP.Period=2, RPA.FebNetBilled, IIF(AP.Period=3, RPA.MarNetBilled, IIF(AP.Period=4, RPA.AprNetBilled, IIF(AP.Period=5, RPA.MayNetBilled,
IIF(AP.Period=6, RPA.JunNetBilled, 0)))))) +
IIF(AP.Period=7, RPA.JulNetBilled, IIF(AP.Period=8, RPA.AugNetBilled, IIF(AP.Period=9, RPA.SepNetBilled, IIF(AP.Period=10, RPA.OctNetBilled,
IIF(AP.Period=11, RPA.NovNetBilled, IIF(AP.Period=12, RPA.DecNetBilled, 0)))))) as BilledAmt
from AccountPeriod AP
INNER JOIN REVENUE_PROJECTION_ANALYSIS RPA ON RPA.YEAR = AP.Year
They don't need to be nested at all:
SELECT @Return = CASE WHEN LEFT(@BPartyNo, 4) = '+610' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 5, LEN(@BPartyNo)) ) )
WHEN LEFT(@BPartyNo, 3) = '+61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 4, LEN(@BPartyNo)) ) )
WHEN LEFT(@BPartyNo, 2) = '61' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 3, LEN(@BPartyNo)) ) )
WHEN LEFT(@BPartyNo, 6) = '001161' THEN SUBSTRING('0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)), 11, LEN( '0' + SUBSTRING(@BPartyNo, 7, LEN(@BPartyNo)) ) )
WHEN ( LEFT(@BPartyNo,2) = '01' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
WHEN ( LEFT(@BPartyNo,2) = '02' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
WHEN ( LEFT(@BPartyNo,2) = '03' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
WHEN ( LEFT(@BPartyNo,2) = '04' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
WHEN ( LEFT(@BPartyNo,2) = '07' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
WHEN ( LEFT(@BPartyNo,2) = '08' AND LEN(@BPartyNo) > 10 ) THEN SUBSTRING(@BPartyNo, 11, LEN(@BPartyNo))
ELSE ''
END
Personally, I would restructure your code so that you make sure the data in sanitized upon input, rather than trying to sanitize it now (when it's clearly too late...). Or at least do the conversion in your client language (i.e. in whatever is calling this sproc), which is hopefully more suited to the task of string manipulation than T-SQL is.