Conversion failed when converting the nvarchar value 'value,value' to int

Question

update Quotation_T  
set DOID = CASE 
              WHEN DOID = '' THEN 19
              WHEN DOID LIKE '%10%' THEN DOID       
              WHEN DOID != '' THEN (DOID + ',' + '20')
           END        

I get an error

Conversion failed when converting the nvarchar value '19,20' to data type int

Answer 1

Most likely, DOID is a nvarchar(n) column - then it's obvious: your CASE expression must return the same datatype for all paths, and since the first WHEN clause returns an INT, all other paths also must return an INT, and if they don't, SQL Server will convert their return values to INT which causes the exception.

Also: since you're updating the DOID column, you should use its native data type - so your first WHEN clause should return a nvarchar(n) instead of an INT.

Try this code instead:

UPDATE Quotation_T  
SET DOID = CASE 
              WHEN DOID = '' THEN CAST(19 AS NVARCHAR(20))
              WHEN DOID LIKE '%10%' THEN DOID       
              WHEN DOID != '' THEN (DOID + N',20')
           END    

Now, all WHEN clauses properly return the datatype that DOID being updated expects, and no implicit conversions are necessary.

Answer 2

I would strongly recommend writing the code as:

update Quotation_T  
    set DOID = (case when doid = '' then '19'
                     else (DOID + ',20')
                end)
    where doid not like '%10%' or doid is null;

This does not even attempt to update the rows that do not need to be updated.

I would also make the observation that you seem to be storing comma-delimited strings in a single column. If so, I would strongly urge you to reconsider your data model, and use a separate junction table instead. SQL is not really designed to store lists of things in strings. This (and other questions you ask) show that mixing types and storing multiple values in a column just create unnecessary problems.