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...

Basic SQL Query Question....

Can someone please tell me how I do the following...
I have a variable @.Date which is a year... EG: 2005 what I want to be able
to do is return all the records found in a table which match the @.Date
variable!
E.G:
IF @.Date = 2005
All the records added to the database which match the year 2005 from the
field DateAdded are returned!
See example below!
Thanks for any help!
CREATE PROCEDURE dbo.GetVVIssue
(
@.FileDescription nvarchar (255),
@.Date Datetime
)
AS
SELECT fileID, FileName, FileDescription, DateAdded
FROM
tblFiles
WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
ORDER BY DateAdded ASC
GOTim
What is a datatype of the Dateadded column?
If you store into a variable @.date only a year why you are defined it as
datetime
declare @.date int --or char(4)
SELECT fileID, FileName, FileDescription, DateAdded
FROM
tblFiles
WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
ORDER BY DateAdded ASC
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:BF72BABE-2F2E-4CE4-87A2-F015CE3C67E9@.microsoft.com...
> Can someone please tell me how I do the following...
> I have a variable @.Date which is a year... EG: 2005 what I want to be able
> to do is return all the records found in a table which match the @.Date
> variable!
> E.G:
> IF @.Date = 2005
> All the records added to the database which match the year 2005 from the
> field DateAdded are returned!
> See example below!
> Thanks for any help!
>
> CREATE PROCEDURE dbo.GetVVIssue
> (
> @.FileDescription nvarchar (255),
> @.Date Datetime
> )
> AS
> SELECT fileID, FileName, FileDescription, DateAdded
> FROM
> tblFiles
> WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
> ORDER BY DateAdded ASC
> GO
>|||DateAdded has a datatype of DateTime!
But how do I return all the records found in a table which match the @.Date
variable in the DateAdded column?
Thanks
"Uri Dimant" wrote:

> Tim
> What is a datatype of the Dateadded column?
> If you store into a variable @.date only a year why you are defined it as
> datetime
> declare @.date int --or char(4)
> SELECT fileID, FileName, FileDescription, DateAdded
> FROM
> tblFiles
> WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
> ORDER BY DateAdded ASC
>
>
> "Tim::.." <myatix_at_hotmail.com> wrote in message
> news:BF72BABE-2F2E-4CE4-87A2-F015CE3C67E9@.microsoft.com...
>
>|||Tim
SELECT fileID, FileName, FileDescription, DateAdded
FROM
tblFiles
WHERE FileDescription = @.FileDescription AND YEAR(DateAdded) = YEAR(@.Date)
ORDER BY DateAdded ASC
Note: An optimizer won't use an index on DateAdded column.
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:66F29AF7-64A0-49BD-AEC2-440F91648DB7@.microsoft.com...
> DateAdded has a datatype of DateTime!
> But how do I return all the records found in a table which match the @.Date
> variable in the DateAdded column?
> Thanks
>
> "Uri Dimant" wrote:
>
able
the|||In your example @.date is a DATETIME not a numeric so it cannot be equal
to the value 2005. It could be 2005-01-01 00:00:00.000 or 2005-12-31
00:00:00.000 or some other date but if you try to pass it the numeric
value 2005 then @.date will actually take the value of a date in 1905
because the numeric is interpreted as X number of days since
1900-01-01.
What you need is a range query, such as:
WHERE dateadded >= '20050101'
AND dateadded < '20060101'
You could parameterize this by passing the date as '20050101':
WHERE dateadded >= @.year_start_date
AND dateadded < DATEADD(YEAR,1,@.year_start_date)
or you could obviously pass both the start and end dates as parameters.
If you really eanted to pass just a numeric value then change the
parameter to a numeric (@.year) and try this:
WHERE dateadded >= DATEADD(YEAR,@.year-2000,'20000101')
AND dateadded < DATEADD(YEAR,@.year-1999,'20000101')
Hope this helps.
David Portas
SQL Server MVP
--