Update multiple rows in SQL Server with IN clause

Question

I have a stored procedure like this:

CREATE PROCEDURE Resume 
    @UserID nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    update Candidate set Approved = 'y' where UserId_I in (@UserID)
END
GO

In this the @UserID is in string format. I send the value of parameter @UserID is 1,2,3 from code as string.

I got error as

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

Please solve this

Multiple separate arguments to `in` are logically different from a *single* string argument that *happens* to contain commas. SQL, in common with practically every other programming language, won't magically decide to inspect the string and change this. The proper solution to this is to not *pass* the string in the first place - pass (ideally) a Table-Valued parameter or an XML document - things designed to contain multiple values. No-doubt you'll get answers (or close votes) pointing towards creating a split function on the server, but it's really the wrong approach. — Damien_The_Unbeliever, Jul 12 '13 at 07:33

score 3 · Answer 1 · edited May 23 '17 at 12:05

You can do it in multiple ways. You can either define a table type and pass the list of user IDs via that table type (See this question: INSERT using LIST into Stored Procedure).

You can also split up the string at the commas in a WHILE loop and add the IDs to a temporary table. Then you can use ... IN (SELECT UID FROM @tempTable). Rough algorithm:

DECLARE @tempTable TABLE (UID INT)

WHILE ([Length of @UserID > 0])
BEGIN
    IF ([@UserID contains comma?])
    BEGIN
        [GET STRING UNTIL COMMA]
        [CONVERT TO INT AND INSERT INTO @tempTable]
        [REMOVE STRING AND COMMA FROM @UserID]
    END ELSE
    BEGIN
        [CONVERT @UserID TO INT AND INSERT INTO @tempTable]
        SET @UserID = ''
    END
END

You can also use the EXEC approach, but I tend to not use EXEC with parameters passed from the outside world - you open your code to SQL injections. The minimum check I'd perform is whether the @UserID parameter contains only numbers, commas and spaces.

score 2 · Answer 2 · answered Jul 12 '13 at 07:36

2

Please update using EXECUTE (Transact-SQL) command.

CREATE PROCEDURE Resume 
    -- Add the parameters for the stored procedure here
    @UserID nvarchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    exec ('update Candidate set Approved=''y'' where UserId_I in ('+@UserID+')')
END
GO

answered Jul 12 '13 at 07:36

TechDo

18,398
3
51
64

That's also an option of course, but please mind the SQL-injection possibility here. ;-) – Wim.van.Gool Jul 12 '13 at 07:39

score 2 · Accepted Answer · answered Jul 12 '13 at 07:47

Try using temporary table which has better performance than sub query

DECLARE @userIds NVARCHAR(MAX)
SET @userIds ='1,2,3,4,5,6'

CREATE table #TempUser 
(
    userId Int
) 
DECLARE @SplitOn VARCHAR(1)
SET @SplitOn = ','

While (Charindex(@SplitOn,@userIds)>0)
Begin
    Insert Into #TempUser (userId)
    Select 
        userId = ltrim(rtrim(Substring(@userIds ,1,Charindex(@SplitOn,@userIds )-1)))
    Set @userIds = Substring(@userIds,Charindex(@SplitOn,@userIds)+1,len(@userIds))
End

Insert Into #TempUser (userId)
Select Data = ltrim(rtrim(@userIds))

SELECT * FROM #TempUser

Now you can update your records using this temporary table.

Aleksandr Fedorenko · Answer 4 · 2013-07-12T09:10:36.560

For a splitting @UserID parameter you can use the XQuery value() method

CREATE PROCEDURE Resume 
  @UserID nvarchar(100)
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE Candidate 
  SET Approved = 'y' 
  WHERE UserId_I IN 
    (SELECT Split.a.value('.', 'int')
     FROM (SELECT CAST ('<M>' + REPLACE(@UserID , ',', '</M><M>') + '</M>' AS XML) AS val    
           ) AS A CROSS APPLY val.nodes('/M') AS Split(a)
     )
END
GO

See demo on SQLFiddle

score 0 · Answer 5 · answered Jul 12 '13 at 07:38

The error occurs because you cannot use a string (@UserID) as the right-hand operator of the IN-operator. The IN-operator requires a SET as the right-hand operator - in this case you'll need a set of ints to tell whether UserId_I is part of that set.

There are two things you can do here: either use a table-value parameter for the @UserID-parameter, or write a function that converts/splits the @UserID-string into a table/set of ints, in which case you'll get something like:

...WHERE @UserId_I IN dbo.ParseIntSet(@UserId);

score 0 · Answer 6 · answered Jul 12 '13 at 08:24

You can try a demo given below...

CREATE PROCEDURE [dbo].[Proc_Name] @CSVConnectivityIds NVARCHAR(MAX)
WHILE len(@CSVConnectivityIds) > 0
    BEGIN
        INSERT INTO tblName
        VALUES (left(@CSVConnectivityIds, charindex(',', @CSVConnectivityIds + ',') - 1))
        SET @CSVConnectivityIds = stuff(@CSVConnectivityIds, 1, charindex(',', @CSVConnectivityIds + ','), '')
    END

I hope this will help you

Update multiple rows in SQL Server with IN clause

6 Answers6

Linked