SQL Server - Statement (s) could not be prepared. Case expressions may only be nested to level 10

Speak guys!
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:

Understanding and simulating the problem

The query in question was something like this:

So, I will try to reproduce this scenario in my test environment:

Analyzing the estimated execution plan (the real one does not open), we identified that SQL Server is using the “Remote Query” operator to make the query and this has been changed to include several nested cases, as shown in the print below:

OK .. I was able to simulate the error .. I will connect to the data source instance to test if this query will work locally or if only this happens when the data comes from Linked Server:

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 subquery

I tried using a subquery to try to ignore this limitation of more than 10 conditions in a case with data coming from Linked Server.

Using CTE

I tried using a CTE to try to ignore this limitation of more than 10 conditions in a case with data coming from Linked Server.

Using Multiple COALESCE CASES

When I tried to use multiple cases and a COALESCE function to merge data from multiple cases into a single column, that's where I was able to successfully query:

Using OPENQUERY

When I tried to use OPENQUERY to query remote data and build CASE, I was also successful in performing the query:

Post Summary:

• 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!
Big hug!