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 youre done!!!

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

 

[mike55]

Thanks Joe.

Mike55