Hello,
I want to pass in to my procedure an input string with values delimited by a semicolon
'100;200;300;400;'
and I want to parse this string and use the individual values in an insert statement loop within a  transaction.
How can I do this?
Thanks,
Burak
 
 
 
 

refer the links

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=653&lngWId=5

Check out the Sample "Parse a comma delimited comma.."
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1110&lngWId=5
http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp
http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=1110&lngWId=5

hello,
i'm going to use the split function in
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=653&lngWId=5
how do i  store the returned strings in my stored procedure after i call the split function?
thanks,
burak
 

hello,
i'm going to use the split function in
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=653&lngWId=5
how do i  store the returned strings in my stored procedure after i call the split function? how do i use these values ina transaction loop.
thanks,
burak

will take the entire delimited string and return a table that you can program against:
CREATE  FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(    
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)   
)
AS
BEGIN
DECLARE @index int 
SET @index = -1 
WHILE (LEN(@text) > 0) 
  BEGIN  
    SET @index = CHARINDEX(@delimiter , @text)  
    IF (@index = 0) AND (LEN(@text) > 0)  
      BEGIN   
        INSERT INTO @Strings VALUES (@text)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   
        SET @text = RIGHT(@text, (LEN(@text) - @index))  
      END  
    ELSE 
      SET @text = RIGHT(@text, (LEN(@text) - @index)) 
    END
  RETURN
END
-- Example usage in a stored proc, to insert into Table variable:
/*
DECLARE @WORDS TABLE 
(
ID INT IDENTITY (1,1),
string varchar(100)
)
 
INSERT INTO @WORDS
SELECT   VALUE AS WORD  FROM dbo.fn_Split( @WordsToInsert,' ')
*/

Peter,
I wrote a stored procedure using your function and it works fine. I was wondering how I could make the while statement at the end more elegant and add transaction functionality into it?
CREATE PROCEDURE dbo.wsp_Roles_AssignSchoolsByUserId
@UserId uniqueidentifier,
@Schools varchar(8000)
AS
-- declare a table to hold school names 
DECLARE @USER_SCHOOLS TABLE 
(
ID INT IDENTITY (1,1),
SCHOOL char(4)
)
 
-- parse the delimited list of schools into individual schools 
-- and insert the school names into the user_schools table
INSERT INTO @USER_SCHOOLS
SELECT   VALUE AS SCHOOL  FROM dbo.fn_Split( @Schools,';')
-- check to see if table has any records
DECLARE @COUNT int 
SELECT @COUNT = count(*)  FROM @USER_SCHOOLS
IF @COUNT > 0
BEGIN
   -- select individual school names and insert them along with the user id
   -- into  tbl_UsersInSchools
   DECLARE @SCHOOL char(4)
   
    WHILE( @COUNT > 0 )
    BEGIN
                 
                 SET @SCHOOL = (SELECT TOP 1 SCHOOL from  @USER_SCHOOLS)
                 INSERT INTO tbl_UsersInSchools VALUES (@UserId, @SCHOOL)
    DELETE FROM @USER_SCHOOLS WHERE SCHOOL = @SCHOOL 
                SET @COUNT = @COUNT - 1
    END
END
GO

Here is some SQL code which might be useful for you, you can customize it as per your requirements. This does not use a table variable so should have a faster run time.

declare @school  varchar(8000)
declare @pos1 int
declare @pos2 int
declare @err_code INT
 
set @pos1 = 1
set @pos2 = 1
set @school = '1232;23;45;56;746;'
set @pos2 = charindex(';',@school)

while (@pos1<@pos2)
begin
   begin transaction
        print substring(@school,@pos1,@pos2-@pos1)  
       -- Insert statement
          if (@@Error>0)
          begin
                 @err_code = @@Error
          end
        -- Update Statement
          if (@@Error>0)
          begin
                 @err_code = @@Error
          end
        -- Delete statement
          if (@@Error>0)
          begin
                 @err_code = @@Error
          end
  
    commit transaction
    if(@err_code>0)
    begin
            PRINT 'Unexpected error occurred!' + STR(@err_code)
             Rollback transaction   
   end     
   set @pos1 = @pos2 + 1 
   set @pos2 = @pos2+charindex(';',substring(@school,@pos1,len(@school)- @pos1+1))
end
Hope this helps.