UPDATE multiple records using WHERE/IN clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE multiple records using WHERE/IN clause

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-10-09 : 14:21:13

Here's my table:

----------------------------------------------
tblUsers
----------------------------------------------
UserID | Moderated
----------------------------------------------

Say I have a list of UserIDs as a string:
"1,3,7,8,14,20"

I want to update users with those IDs by setting the Moderated attribute to true.

Here's my stored procedure:

@UserIDs nvarchar(300)

AS

UPDATE tblUsers
SET Moderated = 1
WHERE UserID IN @UserIDs

But this throws an error, saying:
"Incorrect syntax near '@UserIDs'"

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-09 : 14:28:36

create procedure testproc @UserIDs varchar(300)
as
declare @sqlstr varchar(8000)

set @sqlstr = 'UPDATE tblUsers SET Moderated = 1 WHERE UserID IN ('+@UserIDs+')'

exec(@sqlstr)

exec testproc '1,3,7,8,14,20'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 00:36:13

no need of dynamic sql. you can do like this

create procedure testproc @UserIDs varchar(300)
as
UPDATE tblUsers 
SET Moderated = 1 
WHERE ','+@UserIDs+',' LIKE '%,'+CAST(UserID AS varchar(20))+',%'
GO

exec testproc '1,3,7,8,14,20'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-10 : 03:29:51

Also search for Array+sql server in google

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources