Microsoft SQL Server forum - Help with a list variable

Author

Help with a list variable

Erich93063

2005-08-03, 1:25 pm

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
@strAssignedTo varch
ar (200) = NULL

AS

CREATE TABLE #tblAssignedTo (
strAssignedToID varc
har(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,@star
tingPosition)
SET @lengthOfString = @parseString1 - @startingPosition
IF @lengthOfString > 0
BEGIN
SET @strAssignedToID = SUBSTRING(@strAssign
edTo, @startingPosition,
@lengthOfString)
SET @startingPosition = @parseString1 + 1
END
ELSE
BEGIN
SET @parseString1 = 0
SET @strAssignedToID = ''
END
IF @strAssignedToID != ''
BEGIN
INSERT #tblAssignedTo(strAs
signedToID)
VALUES(@strAssignedT
oID)
END
END

SELECT * FROM #tblAssignedTo
GO

Chandra

2005-08-03, 8:24 pm

hi
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.droptable.com ***

Hugo Kornelis

2005-08-03, 8:24 pm

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)

Sponsored Links