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

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

DOID

nvarchar(n)

CASE

WHEN

INT

INT

INT

2 Answers
2

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.

DOID

nvarchar(n)

CASE

WHEN

INT

INT

INT

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.

DOID

WHEN

nvarchar(n)

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.

WHEN

DOID

19

N'19'

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.