Loops and building comma delimited strings

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @.NumberingType varchar(10)
DECLARE @.TotalSum int
DECLARE @.DoorLineItemID int
DECLARE @.Quantity int
DECLARE @.SeedInt int

SET @.SeedInt = 1

SELECT @.TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity
DECLARE @.RefNumberLine varchar(1024)
SET @.RefNumberLine = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN

WHILE @.SeedInt <= @.Quantity
BEGIN

SET @.RefNumberLine = @.RefNumberLine + CONVERT(varchar, @.SeedInt, 101) + ', '
SET @.SeedInt = @.SeedInt + 1

END
PRINT @.RefNumberLine

SET @.SeedInt = @.Quantity + @.SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @.SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @.Quantity + @.SeedInt, 101)

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity

END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor

This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3

When I really want the results described above.

What am I doing wrong?

Thanks!

You really need to post a table structure and some data for us to use to try this out. That's a lot of variables with no data to reference to try out.|||solved. Thanks for your input.

Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
Thanks
This should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thanks

Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
ThanksThis should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thanks

Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
ThanksThis should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thankssql

Loop through table using column id instead of name

I need to generate a comma delimited file that is a copy of a row in a table
.
I would like to be able to use the column id instead of the column name to d
o
this. i would like to be able to pass a table name to the stored procedure
and not have to have the column names hard coded.
Is this even possible?
I know it is possible to do in C++ or VB, but can it be done as a stored
procedure.
Thanks for your help,
KenIt seems to me it's much more efficient to do it in the calling application.
String and file handling isn't T-SQL's strong suit.
Ken Holzer wrote:
> I need to generate a comma delimited file that is a copy of a row in a tab
le.
> I would like to be able to use the column id instead of the column name to
do
> this. i would like to be able to pass a table name to the stored procedure
> and not have to have the column names hard coded.
> Is this even possible?
> I know it is possible to do in C++ or VB, but can it be done as a stored
> procedure.
> Thanks for your help,
> Ken