Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005
    Location
    Kansas
    Posts
    11

    Red face Unanswered: CSV not being parsed in While loop

    I'm attempting to use a stored procedure in SQL Server 2000.
    I have an asp.net application that sends a comma delimited string of integers as a varchar to a stored procedure. Prior to sending them I remove any duplicates in the string and then use Cstr before sending it off. Prior to removing the duplicates the while loop worked fine. It was just that it was inserting dups which I wanted to get rid of prior to inserting them.

    The SP then moves through the csv (varchar(1024) in a while loop and performs an insert. like this:
    Code:
    if charindex(@ClassList,@Delim) = 0 
    	begin
    	Insert into TrainingAttendee(TrainingClassID, StaffRosterID, EnteredBy) values (@TrainingClassID, @ClassList, @EnteredBy);
    	end
    else
    BEGIN
    	while len(@ClassList) > 0
    	  BEGIN
    		if charIndex(@Delim, @ClassList) = 0
    		  --process last entry then get out
    		   begin
    	   		Insert into TrainingAttendee(TrainingClassID, StaffRosterID, EnteredBy) values (@TrainingClassID, @ClassList, @EnteredBy);
    		    BREAK
    		  end
    		else
    		--process entry 
    		Insert into TrainingAttendee(TrainingClassID, StaffRosterID, EnteredBy) values (@TrainingClassID, left(@ClassList,charIndex(@delim, @ClassList)-1), @EnteredBy);
    		--return unprocesed part of string	
    		set @ClassList = Right(@ClassList,(Len(@ClassList)-charIndex(@Delim, @ClassList)))
    	   END
    
    END
    return
    The error I am getting back is " Syntax error converting the varchar value '1,2,3,4' to a column of datatype int"

    It appears that the looping isn't working and its just sticking the whole string into the column.

    I'm thoroughly confused by this. I thought it might be an issue with the varchar string I'm sending but I'm not sure anymore. I have a similar ttest loop that works fine when printing the values to the screen. Here it is:
    Code:
    declare @ClassList varchar(1024);
    set @ClassList = '1,2,213,23,512,54,87,p,65,8';
    declare @srid int;
    declare @ctr int;
    set @ctr = 0
    declare @delim char(1)
    set @delim = ','
    
    	if charIndex(@delim, @ClassList) = 0 print 'no delim found'
    while len(@ClassList) > 0
      BEGIN
    	if charIndex(@delim, @ClassList) = 0
    	  --process last entry then get out
    	   begin
    	   print @ClassList
    	  set @ctr = @ctr + 1
    	  BREAK
    	  end
    	--process entry 
    	print(left(@ClassList,charIndex(@delim, @ClassList)-1))
    	set @ctr = @ctr + 1
    	--return unprocesed part of string	
    	set @ClassList = Right(@ClassList,(Len(@ClassList)-charIndex(@delim, @ClassList)))
    print 'remaining: ' + @ClassList
      END
    Anyone have any ideas?
    Incidentally, this will be used by a few people with relatively small amounts of data, perhaps 20 loops at most.
    Last edited by Kayaker411; 03-20-09 at 09:32.

  2. #2
    Join Date
    Mar 2005
    Location
    Kansas
    Posts
    11
    I got it to work. For anyone else who is dumb enouff to try this here is the working code:
    Code:
    if charindex(@Delim, @ClassList) = 0 
    	begin
    	Insert into TrainingAttendee(TrainingClassID, StaffRosterID, EnteredBy) values (@TrainingClassID, @ClassList, @EnteredBy);
    	end
    else
    BEGIN
    	while len(@ClassList) > 0
    	begin
    		if charIndex(@Delim, @ClassList) = 0
    		  --process last entry then get out
    		   begin
    	   		Insert into TrainingAttendee(TrainingClassID, StaffRosterID, EnteredBy) values (@TrainingClassID, @ClassList, @EnteredBy);
    		    BREAK
    		  end
    		else
    		--process entry 
    		Insert into TrainingAttendee(TrainingClassID, StaffRosterID, EnteredBy) values (@TrainingClassID, left(@ClassList,charIndex(@Delim, @ClassList)-1), @EnteredBy);
    		--return unprocesed part of string	
    		set @ClassList = Right(@ClassList,(Len(@ClassList)-charIndex(@Delim, @ClassList)))
    	 end
    
    END
    return

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •