Help with a list variable

I have made the following test stored proc that all it does right now
is accept a string variable that will be a list ie. :5,9,6,13. Right
now all the SP is doing is creating a temp table to store the values in
the list, then looping through the list and inserting the values into
the temp table, then selecting all the records from teh temp table.
It's WORKING however i must have done something wrong because it's not
inserting ALL the values. If the list is "5,9,6,13" only 5, 9 and 6
will get inserted, not the 13. if I pass in the list like this
"5,9,6,13," with an extra comma at the end, they all get inserted fine
but thats not how the strings will be coming in. Here's what I have so
far:

CREATE PROCEDURE sp_searchTaskTest

-- in params
@.strAssignedTovarchar (200) = NULL

AS

CREATE TABLE #tblAssignedTo (
strAssignedToIDvarchar(10)
)

-- initialize variables
DECLARE @.lengthOfString int
DECLARE @.startingPosition int
DECLARE @.parseString1 int
DECLARE @.strAssignedToID varchar(10)

SET @.startingPosition = 0
SELECT @.parseString1 = CHARINDEX (',', @.strAssignedTo,1)
WHILE ( @.parseString1 > 0 )
BEGIN
SELECT @.parseString1 = CHARINDEX (',',
@.strAssignedTo,@.startingPosition)
SET @.lengthOfString = @.parseString1 - @.startingPosition
IF @.lengthOfString > 0
BEGIN
SET @.strAssignedToID = SUBSTRING(@.strAssignedTo, @.startingPosition,
@.lengthOfString)
SET @.startingPosition = @.parseString1 + 1
END
ELSE
BEGIN
SET @.parseString1 = 0
SET @.strAssignedToID = ''
END
IF @.strAssignedToID != ''
BEGIN
INSERT #tblAssignedTo(strAssignedToID)
VALUES(@.strAssignedToID)
END
END

SELECT * FROM #tblAssignedTo
GOhi
looks like u need to make some changes in the code.

else what u can do is,

@.strAssignedTo = @.strAssignedTo + ','

in the first line of the SP. This is not correct but a quick fix

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||On 3 Aug 2005 10:38:43 -0700, Erich93063 wrote:

>I have made the following test stored proc that all it does right now
>is accept a string variable that will be a list ie. :5,9,6,13. Right
>now all the SP is doing is creating a temp table to store the values in
>the list, then looping through the list and inserting the values into
>the temp table, then selecting all the records from teh temp table.
>It's WORKING however i must have done something wrong because it's not
>inserting ALL the values. If the list is "5,9,6,13" only 5, 9 and 6
>will get inserted, not the 13. if I pass in the list like this
>"5,9,6,13," with an extra comma at the end, they all get inserted fine
>but thats not how the strings will be coming in. Here's what I have so
>far:

Hi Erich,

http://www.sommarskog.se/arrays-in-sql.html

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)