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

AS


CREATE TABLE #tblAssignedTo (
strAssignedToID varchar(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
GO

.

Relevant Pages

  • Re: getting values from inserted table
    ... > declare @Var1 int ... > vast majority of time when you're testing your trigger you'll be ... > inserting a single row at a time, but that does not mean that a ...
    (microsoft.public.sqlserver.programming)
  • Re: getting values from inserted table
    ... declare @Var1 int ... vast majority of time when you're testing your trigger you'll be ... inserting a single row at a time, but that does not mean that a ...
    (microsoft.public.sqlserver.programming)
  • Re: Multiple insert in one pass
    ... CREATE TABLE #temp(col INT) ... declare @tablename SYSNAME ... SELECT col FROM #temp ...
    (microsoft.public.sqlserver.programming)
  • Re: Variables make Stored Procedure SLOW
    ... Use a table variable instead of the temp table Chris. ... temp table after you declare and set the other parameters. ... > @sp int, ... > FULL OUTER JOIN #COGS ...
    (microsoft.public.sqlserver.programming)
  • Re: declare temp tables with @ instead of #
    ... > declare @temp table(_id int IDENTITY(1,1), user_id int) ... > declare @curUserID int ...
    (microsoft.public.sqlserver.programming)