batch update in stored procedure

The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @.userIDs which has the following format:

101, 102, 103

I got the error message:

Syntax error converting the varchar value '101, 102, 103' to a column of data type int.

Obviously, UserID has datatype int. How can I write this SP?

CREATE PROCEDURE dbo.MySP
@.userIDs varchar(100)
AS
SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE Users SET IsActive = 1 WHERE UserID IN (@.userIDs)

IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFFOriginally posted by gyuan
Obviously, UserID has datatype int.

Obviousley it doesn't...

Check this out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string

Use the UDF that's posted in there|||You could use dynamic query - sp_executesql or execute it.|||Originally posted by Brett Kaiser
Use the UDF that's posted in there

Brett,

I read the posters from the link you posted but I'm not very clear how to use UDF on my stored procedure since UDF is new to me. Can you give me some hint? UserID is the primary key in the table Users and its datatype is int. Thanks.|||Cut and paste the following code in to QA and run it...

USE Northwind
GO

CREATE FUNCTION CSVTable(@.Str varchar(7000))
RETURNS @.t table (numberval int, stringval varchar(100), DateVal datetime)
AS
BEGIN
DECLARE @.i int, @.c varchar(100);
SELECT @.Str = @.Str + ',', @.i = 1, @.c = '';
WHILE @.i <= LEN(@.Str)
BEGIN
IF substring(@.Str,@.i,1) = ','
BEGIN
INSERT INTO @.t(numberval, stringval, DateVal)
VALUES ( CASE WHEN ISNUMERIC(@.c)=1 THEN @.c ELSE Null END
, RTRIM(LTRIM(@.c))
, CASE WHEN ISDATE(@.c)=1 THEN @.c ELSE Null END)
SET @.c = ''
END
ELSE

SET @.c = @.c + substring(@.Str,@.i,1)
SET @.i = @.i +1
END
RETURN
END
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

INSERT INTO myTable99(Col2)
SELECT 100 UNION ALL SELECT 101 UNION ALL SELECT 102 UNION SELECT 103 UNION ALL
SELECT 104 UNION ALL SELECT 105 UNION ALL SELECT 106 UNION SELECT 107 UNION ALL
SELECT 108 UNION ALL SELECT 109 UNION ALL SELECT 110
GO

DECLARE @.userIDs varchar(100)
SELECT @.userIDs = '101,102,103'

SELECT *
FROM myTable99
WHERE Col2 IN (SELECT StringVal FROM dbo.CSVTable(@.userIDs))
GO

DROP FUNCTION CSVTable
DROP TABLE myTable99
GO

Thank you Dr. Cross Join

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=6859|||I run it and get the result I need. So now do I need to add the function to the stored procedure?|||Just make you're WHERE Clause look like mine...

Just need to make sure the udf is in the same enviroment where you release the procedure...

Get it?|||Yes, it works. That is a great function. Thank you, Brett.

By the way, can you tell me what the difference is between UDF and dynamic SQL? Performance?|||Yeah, go to the link and give thanks to Jeff..

Use dynamic sql as a last resort...and never in application code...

I use it for admin support...but it's not released...