Conversion failed when converting the varchar value to data type int using in clause in SQL Server

Question

My Stored procedure is giving error

"Conversion failed when converting the varchar value to data type int".

What I am trying to do is to use the IN clause, the values inside the IN clause are string here and separated by comma. The stored procedure is -

ALTER PROCEDURE [dbo].[sp_UpdateBulkPageStatus] 
@Id Varchar(50),
@Status bit
AS             
BEGIN
    UPDATE Page SET Status=@Status WHERE Id in (@id)    
END

It given error on running - [dbo].[sp_UpdateBulkPageStatus] '24,25',1 Now when I am just running the update query with values on it {UPDATE Page SET Status=1 WHERE Id in (24,25)} then it runs perfectly. What is the problem?

The problem is that you're passing a list (or array) of values and SQL Server doesn't know how to process it as there is no array type. You need to either pass the data in some other way (like a table valued type for instance) or split the values inside the procedure (using some function for example). I would recommend you to read this article: [Arrays and Lists in SQL Server 2008](http://www.sommarskog.se/arrays-in-sql-2008.html) — jpw, Jun 27 '15 at 12:18

Sameer · Answer 1 · 2015-06-27T13:05:33.080

3

By now you must have realised the mistake.If you are passing only comma separated ids then want to avoid parsing one alternative is to use dynamic query.

declare @dynamicUpdateQuery nvarchar(max)
set @dynamicUpdateQuery='UPDATE Page SET Status='+@Status+ ' WHERE Id in (' +@id + ')';
EXECUTE sp_executesql @dynamicUpdateQuery

edited Jun 27 '15 at 13:05

answered Jun 27 '15 at 12:45

Sameer

3,124
5
30
57

i tried your solution it gives - Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. – yogihosting Jun 27 '15 at 12:57
can you change the type of dynamicquery from varchar to nvarchar and try.I have updated the code – Sameer Jun 27 '15 at 13:06
Please don't do this. So full of injection possibilities. Pass it in as an array or table or something. – LoztInSpace Jun 27 '15 at 13:11
worked now - @sameer, thanks – yogihosting Jun 27 '15 at 16:15
@loz for bringing out the security info. Thanks. – yogihosting Jun 27 '15 at 16:15

Gordon Linoff · Answer 2 · 2015-06-27T12:49:47.077

My guess is that id is an integer, and you are comparing it to a string. By the rules of SQL Server type conversions, the comparison is done as an integer, not a string, and you get an error. Even if the comparison were done as a string, it still would not be what you want.

So, a correct version would be:

ALTER PROCEDURE [dbo].[sp_UpdateBulkPageStatus] 
    @Id int,
    @Status bit
AS             
BEGIN
    UPDATE Page SET Status=@Status WHERE Id = @id   
END;

That is probably not what you really want the stored procedure to do, because it would only take one id.

There are numerous ways to pass multiple values into a stored procedure. One method is using like:

   WHERE ',' + @ids + ',' like '%,' + cast(Id as varchar(255)) + ',%'

Another method is to find a split() function on the web, and do:

WITH ids as (
      SELEcT id
      FROM dbo.split(@ids, ',') as s(id)
     )
UPDATE Page p
    SET Status = @Status
    WHERE p.Id in (SELECT ids.id FROM ids) ;

There are other methods for passing a list into a stored procedure, such as:

Using a while loop and string manipulation.
Passing in a table parameters.
Using a global temporary table.
Writing dynamic SQL.

The use of in with a string variable is not one of them.

Erland Sommarskog has a rather complete discussion on this topic. If you want to understand different approaches, then read this. (Note: there might be some newer functionality in SQL Server 2014/2016 not covered by this blog.)

score 0 · Answer 3 · answered Jun 27 '15 at 12:18

0

You are passing string value and check numeric value so it is given error If you want run this query you can do some changes.

You can call store procedure to add comm first or last like this

[dbo].[sp_UpdateBulkPageStatus] ',24,25,',1

Change your update query like this

UPDATE Page SET Status=@Status WHERE @id like ','+convert(varchar(20),id)+','

answered Jun 27 '15 at 12:18

Mukesh Kalgude

4,814
2
17
32

i would like to use in clause here. – yogihosting Jun 27 '15 at 12:21
Other wise you can create function for split data to return table. Refer this link. http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx – Mukesh Kalgude Jun 27 '15 at 12:31

Bogdan Sahlean · Answer 4 · 2015-06-27T13:28:13.760

Assuming that you want to compare values from Id colum with a list of values I would use table valued parameters thus:

-- It creates a new data type dbo.IntArray
CREATE TYPE dbo.IntArray AS TABLE (
    IntValue INT
);
GO

-- It alter the stored procedure: @Ids parameters accept multiple values (rows)
ALTER PROCEDURE [dbo].[sp_UpdateBulkPageStatus] 
@Ids dbo.IntArray READONLY,
@Status bit
AS             
BEGIN
    UPDATE [Page] SET Status=@Status 
    WHERE EXISTS (
        SELECT *
        FROM @Ids p WHERE p.IntValue = [Page].Id
    )
    -- or WHERE [Page].Id IN (SELECT p.IntValue FROM @Ids p)
END
GO

-- Test
DECLARE @p1 dbo.IntArray;
INSERT @p1 VALUES (44),(45);
EXEC [dbo].[sp_UpdateBulkPageStatus]  @Ids=@p1, @Status = 0;

Erland Sommarskog has published o full list of options if we want to transmit a list of values here: Arrays and Lists in SQL Server

Conversion failed when converting the varchar value to data type int using in clause in SQL Server

4 Answers4