Why conversion issue occurs

Question

What is wrong with the query? I am trying to convert the int to varchar:

DELETE FROM CM_OfflineMessages 
WHERE CONVERT(INT, CONVERT(VARCHAR(MAX), ID)) IN ('1,2,3')

I get this error:

Conversion failed when converting the varchar value '1,2,3' to data type int.

Edit:

I have edited the query

[dbo].[CM_DeleteOfflineMessageByID] (@ID AS VARCHAR(MAX)) 
  DELETE FROM CM_OfflineMessages
  WHERE CONVERT(VARCHAR(MAX), ID) IN (@ID)

However no compile time issue, but the data is not getting purged.

exec [dbo].[CM_DeleteOfflineMessageByID] @ID = N'''10'',''11'',''12'''

the problem here is the quotes- you are trying to compare the ID with the string '1,2,3' which is where it goes wrong. Remove the quotes and it will work — Myke Black, Aug 15 '17 at 09:49
A single string containing commas is not at all the same thing as multiple strings (or ints, etc) *separated* by commas. SQL, in common with most languages, will not inspect the interior of the first, observe the commas and decide to treat it as if it were the second. Ideally, use a mechanism to properly pass multiple values to the database, or if that's not possible, search for `string split` and your RDBMS product to find plenty of solutions. — Damien_The_Unbeliever, Aug 15 '17 at 10:05
If you need to pass **multiple values** to a procedure to do some work, you should really look at [table-valued parameters](https://www.brentozar.com/archive/2014/02/using-sql-servers-table-valued-parameters/) which are available in SQL Server 2008 and up — marc_s, Aug 15 '17 at 10:26

Sergey Kalinichenko · Answer 1 · 2017-08-15T11:02:39.013

You are trying to convert an int to varchar and back:

CONVERT(VARCHAR(max), ID) makes varchar(max) from an int (ID)
CONVERT(INT, ...) converts the result back to int

Since you have an int on the left of IN, RDBMS tries to convert each element of the list on the right side to int as well. Conversion of '1,2,3' fails, resulting in an error.

If you wish to convert ID to varchar and use varchar literals inside the IN list, drop the outer conversion:

DELETE FROM CM_OfflineMessages WHERE CONVERT(VARCHAR(max), ID) IN ('1', '2', '3')

Of course, there is no reason to do this, except as a learning exercise, because the end result is equivalent to a more efficient version without CONVERT:

DELETE FROM CM_OfflineMessages WHERE ID IN (1, 2, 3)

Edit: You cannot pass a string @ID for a list of items. Use table-valued parameters instead (how?)

@bilal Unfortunately, you cannot do it like that: passing a single `@ID` for an entire array of things does not work. Depending on your RDBMS, you may be able to do it with a [table-typed parameter](https://stackoverflow.com/q/11102358/335858), but generally you need multiple variables `@ID1`, `@ID2`, `@ID3` etc. — Sergey Kalinichenko, Aug 15 '17 at 10:21

score 0 · Answer 2 · answered Aug 15 '17 at 09:46

0

Why not just use

DELETE FROM CM_OfflineMessages WHERE ID IN (1,2,3)

??

answered Aug 15 '17 at 09:46

Myke Black

1,299
15
15

because I cannot append , in int from application end – bilal Aug 15 '17 at 09:51

score 0 · Answer 3 · answered Aug 15 '17 at 10:48

Another option if you don't have a Split/Parse function, here is an inline approach to split your string

Example

Delete From CM_OfflineMessages
 Where ID in (
                Select RetVal = B.i.value('(./text())[1]', 'int')
                From  (Select x = Cast('<x>' + replace(@ID,',','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
              )

Why conversion issue occurs

3 Answers3