I'm trying to update a column in SQL Server 2016 using CASE statement because I have to change the value based on different conditions. The problem is that I have more than 10 conditions and it seems that SQL Server allows for only 10 levels of condition at most. So how can I do this? Here is what I was trying to do:

UPDATE my_table  
SET my_column = CASE 
    WHEN condition1 THEN expression1  
    WHEN condition2 THEN expression2  ...

It is not a linked server and I haven't tried to put 11 conditions in a CASE expression. If you look at the documentation, you will find "SQL Server allows for only 10 levels of nesting in CASE expressions".

  • 4
    Here's a demo of a CASE with 20 conditions working perfectly well. Yours is not a case of nested CASEs, if you pardon the pun. The only way your normal CASE could break is if it had more than 10 conditions and was executed against a linked server, which is why Aaron has asked you about it. (The issue is explained in this thread.) – Andriy M Nov 2 '16 at 20:14
  • 1
    Nested case would be like this rextester.com/UTYN26129 – Martin Smith Nov 2 '16 at 20:14
  • Thank you so much guys, I just realized that I misunderstood the meaning of "nesting"; I confused "levels of nesting" with "levels of condition". The CASE expression with more than 10 conditions works perfectly fine on my database. – Bojie Shuai Nov 2 '16 at 20:46

There are two possibilities here.

  1. There is a limitation with linked servers that only allows a nest level of 10 when using CASE expressions. I imagine you are using a linked server but have simplified that away from your question. You probably have code like this:

    UPDATE LinkedServer.database.dbo.table
      SET col = CASE 
        WHEN cond1  THEN expr1
        WHEN cond2  THEN expr2
        WHEN cond3  THEN expr3
        WHEN cond4  THEN expr4
        WHEN cond5  THEN expr5
        WHEN cond6  THEN expr6
        WHEN cond7  THEN expr7
        WHEN cond8  THEN expr8
        WHEN cond9  THEN expr9
        WHEN cond10 THEN expr10
        WHEN cond11 THEN expr11

    Which leads to the following error message:

    Msg 8180, Level 16, State 1
    Statement(s) could not be prepared.
    Msg 125, Level 15, State 4
    Case expressions may only be nested to level 10.

    A workaround is to use dynamic SQL (or perhaps OPENQUERY):

    DECLARE @sql nvarchar(max) = N'UPDATE dbo.table 
      SET col = CASE
        WHEN cond1 ...
    EXEC LinkedServer.database.sys.sp_executesql @sql;

    A second possibility is to create a stored procedure on the linked server, and call that. It's only when sending a raw query through the linked server that you'll hit this limitation (in the future, please provide all relevant information in the question, such as the actual error message you receive and the fact that you are using a linked server).

  2. Your actual query is different than what you suggest in your question, and it looks more like this:

    UPDATE dbo.table SET col = 
     CASE WHEN cond1  THEN expr1  ELSE 
      CASE WHEN cond2  THEN expr2  ELSE 
       CASE WHEN cond3  THEN expr3  ELSE 
        CASE WHEN cond4  THEN expr4  ELSE 
         CASE WHEN cond5  THEN expr5  ELSE 
          CASE WHEN cond6  THEN expr6  ELSE 
           CASE WHEN cond7  THEN expr7  ELSE 
            CASE WHEN cond8  THEN expr8  ELSE 
             CASE WHEN cond9  THEN expr9  ELSE 
              CASE WHEN cond10 THEN expr10 ELSE 
               CASE WHEN cond11 THEN expr11 ELSE 

    That is what the documentation means by "nesting" of CASE expressions, and will lead to this error message:

    Msg 125, Level 15, State 4
    Case expressions may only be nested to level 10.

When you aren't dealing with a linked server or actual nesting, a simple/searched CASE expression can have many more branches than 10. You should try it and then report back with your actual code and any actual errors you receive.

Not the answer you're looking for? Browse other questions tagged or ask your own question.