Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
Thanks
This should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thanks