Stored Procedures + CSV data

[mike55]

Hi all,

 

Went looking on the net and found the following procedure that takes a comma seperated value as an input parameter and then processes it:

CREATE PROC dbo.GetOrderList2
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #TempList
(
	OrderID int
)

DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
	WHILE @Pos > 0
	BEGIN
		SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
		IF @OrderID <> ''
		BEGIN
			INSERT INTO #TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
		END
		SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
		SET @Pos = CHARINDEX(',', @OrderList, 1)

	END
END	

SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM 	dbo.Orders AS o
	JOIN 
	#TempList t
	ON o.OrderID = t.OrderID
	
END
GO

 

I am trying to modify the code so that it will take to parameters in CSV format: @group and @member. I am attempting to insert the data along with an organization number into a table that is of the following format:

memberID int (4), groupID int (4), and myOrg (254). The procedure now looks as follows:

CREATE PROCEDURE dbo.UpdateTables (
@OrgID nvarchar(254),
@member varchar(254),
@group varchar(254)
)
AS
SET NOCOUNT ON

DECLARE @memberID varchar (10)
DECLARE @groupID varchar (10)
DECLARE @Pos1 int 
DECLARE @Pos2 int

SET @member = LTRIM(RTRIM(@member))+ ','
SET @group = LTRIM(RTRIM(@group))+ ','
SET @Pos1 = CHARINDEX(',', @member, 1)
SET @Pos2 = CHARINDEX(',', @group, 1)

IF REPLACE(@member, ',', '') <> '' and REPLACE(@group, ',', '') <> ''
BEGIN
	WHILE @Pos1 > 0 and @Pos2 > 0
	BEGIN
		SET @memberID = LTRIM(RTRIM(LEFT(@member, @Pos1 - 1)))
		SET @groupId = LTRIM(RTRIM(LEFT(@group, @Pos2 - 1)))
		IF @memberID <> '' and @groupID <> ''
		BEGIN
			INSERT INTO dataTable4 (memberID, groupID, myOrg) VALUES (CAST(@memberID AS int), CAST(@groupID AS int), @OrgID) --Use Appropriate conversion
		END
		SET @member = RIGHT(@member, LEN(@member) - @Pos1)
		SET @Pos1 = CHARINDEX(',', @member, 1)

		SET @member = RIGHT(@group, LEN(@group) - @Pos2)
		SET @Pos2 = CHARINDEX(',', @group, 1)
	END
END
GO

 

The data I am inserting is: @member="1,251,3,4,1,251,3,4,1,251,3,4" and @group="6,6,6,6,7,7,7,7,9,9,9,9" and @OrgID="ST466C513"

 

The problem that is occuring is that the procedure is going into an infiniate loop and is stuck working on the first line of data and inserts @member=6, @group=6 and @orgID="ST466C513".

 

The second problem that arises is if the groupValue is greater that 10, the procedure is unable to perform the type cast when asked.

 

Any suggestions??

 

Mike55

[mike55]

Problem solved:

 

SET @member = RIGHT(@group, LEN(@group) - @Pos2

 

Should be

 

SET @group = RIGHT(@group, LEN(@group) - @Pos2

 

Works perfectly now.

 

Mike55.

[Joe Mamma]

dont know if this will help but it might. . .

 

Its a 'split' function for SQL server:

CREATE FUNCTION SPLIT(@S AS varchar(8000), @DELIM AS varchar(8000))
RETURNS @SPLITTABLE TABLE (ID INT IDENTITY(0,1) PRIMARY KEY, VALUE varchar(8000))
AS
BEGIN
DECLARE @TEMP varchar(8000)
DECLARE @THEVALUE varchar(8000)
SET @TEMP = ISNULL(@S, '')
DECLARE @IDX INT
DECLARE @POS INT
SET @IDX= CHARINDEX(@DELIM, @TEMP)
WHILE @IDX >= 1
BEGIN
SET @POS = @IDX - 1
SET @THEVALUE = SUBSTRING(@TEMP, 1, @POS)
SET @POS = @IDX+LEN(@DELIM)
SET @TEMP = SUBSTRING(LTRIM(RTRIM(@TEMP)), @POS, LEN(@S))
INSERT @SPLITTABLE (VALUE) VALUES(RTRIM(LTRIM(@THEVALUE)))
SET @IDX= CHARINDEX(@DELIM, @TEMP)
END
IF @TEMP <> ''
INSERT @SPLITTABLE (VALUE) VALUES(RTRIM(LTRIM(@TEMP)))
RETURN
END

 

usage:

 

select * from dbo.split('foo,bar,fu,bar', ',')

 

yields:

 ID	VALUE 
---- ------
0	 foo
1	 bar
2	 fu
3	 bar

in your case do:

 

insert into dataTable4 
select cast(t1.Value as int), cast(t2.Value as int), 'ST466C513' 
from dbo.split('1,251,3,4,1,251,3,4,1,251,3,4', ',') t1 
inner Join 
dbo.split('6,6,6,6,7,7,7,7,9,9,9,9', ',') t2
on t1.id = t2.id 

 

and you're done!!!

 

jsut remember to give executable permissions to public for the split function

Edited August 4, 2005 by Joe Mamma

[mike55]

Thanks Joe.

 

Mike55