In this article I would like to share with you a question that was sent to a Whatsapp group, which referred to the error message below when an analyst was trying to build a CASE with more than 10 conditions in a SELECT:
Statement (s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
Understanding and simulating the problem
The query in question was something like this:
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 5 THEN 5
WHEN 6 THEN 6
WHEN 7 THEN 7
WHEN 8 THEN 8
WHEN 9 THEN 9
WHEN 10 THEN 10
WHEN 11 THEN 11
END) AS Teste
Analyzing the estimated execution plan (the real one does not open), we found that SQL Server is using the “Remote Query” operator to make the query and it has been changed to include several nested cases, as shown below:
The same query worked when I logged in to the local instance, even with more than 10 conditions in CASE. That is, I just proved that this error message only happens when we are applying a case from data that comes from a Linked Server. Querying local data, that is, in the same instance, we do not have this restriction, since this time, the operator used is the Clustered Index Scan instead of Remote Query:
Let's look at some alternatives to apply this CASE and avoid this error message:
Using Multiple COALESCE CASES
- If the query does not use Linked Server, you do not need to use this technique. You can use as many conditions as you want in the same case.
- If your query uses Linked Server that points to its own instance, you do not need to use this technique either. It works like a normal query without linked server.
- The COALESCE function accepts multiple conditions, so it is not limited to just 2 cases, they can be multiple (although the complexity of the code is increasing.)
- You can only use up to 9 conditions in each CASE in scenarios where the query is done on remote data.
- Using ELSE NULL at the end of each case is optional.
- Subquery and CTE do not resolve this issue.
- OPENQUERY and OPENROWSET do not have this limitation of 10 CASE conditions either.
Thanks for visiting, I hope you enjoyed the post very objective and even more!