SQL Server - Statements Could Not Be Prepared: Case Expressions May Only Be Nested to Level 10

Hey guys!
In this article I would like to share with you a question that was sent in a Whatsapp group, which was related to the error message below when an analyst was trying to create a CASE with more than 10 conditions in a SELECT:

Understanding and simulating the problem

The query in question was something like this:

SELECT 
    (CASE Id
        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
FROM 
    [DIRCEU-VM\SQL2019].dirceuresende.dbo.Clientes

So, I'll try to reproduce this scenario in my testing environment:

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

OK.. I managed to simulate the error.. I'm going to connect to the data source instance to test if this query will work locally or if this only happens when the data comes from a Linked Server:

The same query worked when I connected to the local instance, even with more than 10 conditions in the CASE. In other words, I just confirmed that this error message only happens when we are applying a case based on data that comes from a Linked Server. Querying local data, that is, data that is in the same instance, we do not have this restriction, since this time, the operator used is Clustered Index Scan instead of Remote Query:

Let's look for 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 a Linked Server.

Using CTE

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

Using Multiple CASES with COALESCE

When I tried to use multiple cases and a COALESCE function to join the data from multiple cases into a single column, that's where I was successful in performing the query:

Using OPENQUERY

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

Post summary:

• If the query does not use Linked Server, there is no need to use this technique. You can use as many conditions as you want in the same case
• If the query uses a Linked Server that points to the instance itself, there is no need to use this technique either. It works as if it were a normal query without a linked server.
• The COALESCE function accepts multiple conditions, so it is not limited to just 2 cases, there can be several (although the complexity of the code increases...)
• You can only use up to 9 conditions in each CASE in scenarios where the query is made on remote data
• The use of ELSE NULL at the end of each case is optional
• Subquery and CTE do not solve this problem
• OPENQUERY and OPENROWSET do not have this limitation of 10 CASE conditions either

Thank you for visiting, I hope you enjoyed the very objective post and see you later!
Big hug!