How to Update a Column Using CASE Statement with More Than 10 Conditions

Question

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".

score 13 · Accepted Answer · edited Apr 13 '17 at 12:42

There are two possibilities here.

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
END;
```
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 ...
    ...
  END;';

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).

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 
END END END END END END END END END END END;

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.

How to Update a Column Using CASE Statement with More Than 10 Conditions

1 Answers1