In this blog post, let’s learn about the error message “125 – Case expressions may only be nested to level %d.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
125 – Case expressions may only be nested to level %d.
Reason for the Error
SQL Server Error Msg 125 is generated when a case expression in a query or statement is nested too deeply. This error message indicates that the maximum level of nesting for case expressions has been exceeded.
The maximum level of nesting for case expressions in SQL Server is 10. This means that you can use up to 10 levels of nested case expressions in a single statement. If you attempt to use more than 10 levels of nesting, SQL Server will generate Error Msg 125.
Let’s have a look at the below example.
SELECT
CASE
WHEN 1 = 1 THEN
CASE
WHEN 2 = 2 THEN
CASE
WHEN 3 = 3 THEN
CASE
WHEN 4 = 4 THEN
CASE
WHEN 5 = 5 THEN
CASE
WHEN 6 = 6 THEN
CASE
WHEN 7 = 7 THEN
CASE
WHEN 8 = 8 THEN
CASE
WHEN 9 = 9 THEN
CASE
WHEN 10 = 10 THEN
CASE
WHEN 11 = 11 THEN 'Result 1'
ELSE 'Result 2'
END
ELSE 'Result 3'
END
ELSE 'Result 4'
END
ELSE 'Result 5'
END
ELSE 'Result 6'
END
ELSE 'Result 7'
END
ELSE 'Result 8'
END
ELSE 'Result 9'
END
ELSE 'Result 10'
END
ELSE 'Result 11'
END
ELSE 'Result 12'
END
This will result with the below error
Msg 125, Level 15, State 3, Line 22
Case expressions may only be nested to level 10.
Solution
The solution to this error message is to simplify the case expression by reducing the level of nesting. This can be done by either simplifying the logic of the case expression or by breaking down the expression into multiple steps using subqueries or temporary tables.
