This Stored procedure receives two arrays and it parses two arrays and inserts the record in the database.
This uses Nested while loops to parse two arrays.
CREATE PROCEDURE ParseArray (@Array VARCHAR(1000),@Array1 VARCHAR(1000),@separator CHAR(1))
AS
BEGIN
SET NOCOUNT ON
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
Declare @TempArray VARCHAR(1000)
SET @TempArray=@Array1
Declare @ArrayCount INT
SET @ArrayCount=LEN(@Array1)
DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
DECLARE @separator_position1 INT -- This is used to locate each separator character
DECLARE @array_value1 VARCHAR(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
SET @Array = @Array + @separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @Array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%',@Array)
SELECT @array_value = LEFT(@Array, @separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
-- SELECT Array_Value = @array_value
SELECT @Array = STUFF(@Array, 1, @separator_position, '')
if @ArrayCount = 0
BEGIN
SET @Array1 = @TempArray + @separator
END
ELSE
BEGIn
SET @Array1 = @Array1 + @separator
END
WHILE PATINDEX('%'+@separator+'%',@Array1)<>0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position1 = PATINDEX('%' + @separator + '%',@Array1)
SELECT @array_value1 = LEFT(@Array1, @separator_position1 - 1)
--print @separator_position
print 'val1='+@array_value+ ' val2='+ @array_value1
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
--SELECT Array_Value1 = @array_value1
SELECT @Array1 = STUFF(@Array1, 1, @separator_position1, '')
SET @ArrayCount= LEN(@Array1)
Insert Into Test(i1,i2) Values(@array_value,@array_value1)
print @Array1
END
-- This replaces what we just processed with and empty string
END
SET NOCOUNT OFF
END
AS
BEGIN
SET NOCOUNT ON
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
Declare @TempArray VARCHAR(1000)
SET @TempArray=@Array1
Declare @ArrayCount INT
SET @ArrayCount=LEN(@Array1)
DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
DECLARE @separator_position1 INT -- This is used to locate each separator character
DECLARE @array_value1 VARCHAR(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
SET @Array = @Array + @separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @Array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%',@Array)
SELECT @array_value = LEFT(@Array, @separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
-- SELECT Array_Value = @array_value
SELECT @Array = STUFF(@Array, 1, @separator_position, '')
if @ArrayCount = 0
BEGIN
SET @Array1 = @TempArray + @separator
END
ELSE
BEGIn
SET @Array1 = @Array1 + @separator
END
WHILE PATINDEX('%'+@separator+'%',@Array1)<>0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position1 = PATINDEX('%' + @separator + '%',@Array1)
SELECT @array_value1 = LEFT(@Array1, @separator_position1 - 1)
--print @separator_position
print 'val1='+@array_value+ ' val2='+ @array_value1
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
--SELECT Array_Value1 = @array_value1
SELECT @Array1 = STUFF(@Array1, 1, @separator_position1, '')
SET @ArrayCount= LEN(@Array1)
Insert Into Test(i1,i2) Values(@array_value,@array_value1)
print @Array1
END
-- This replaces what we just processed with and empty string
END
SET NOCOUNT OFF
END
 |
Link :
http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx
http://www.sqlservercentral.com/articles/Array/70702/
Happy Coding.....!!!!! :)