Loop thru a SQL Table in stored proc?

[Eric Martin]

Hello,

Does anyone know of a way to loop thru a SQL table using code in a stored
procedure?

I need to go thru each record in a small table and build a string using
values from the fields associated with a part number, and I can't find any
way to process each record individually. The string needs to be initialized
with the data associated with the 1st record's part number, and I need to
build the string until a new part number is incurred. Once a new part number
is found in the table, the string is written to a different table and reset
for this next part number in the table. Need to repeat until all records in
the table have been processed.

I use ADO in access 2000 to work thru local recordsets, I just can't find
anyway to do this in a stored SQL procedure.

Thanks for any suggestions, Eric.

[Eric Martin]

Thanks Erland, that worked in the procedure.

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

[posted and mailed, please reply in news]

Eric Martin (er********@cox.net) writes:

Does anyone know of a way to loop thru a SQL table using code in a stored procedure?

Most people here know that you should not do this kind of thing, but
one should always strive for set-based solutions. Then again...

I need to go thru each record in a small table and build a string using
values from the fields associated with a part number, and I can't find
any way to process each record individually. The string needs to be
initialized with the data associated with the 1st record's part number,
and I need to build the string until a new part number is incurred. Once
a new part number is found in the table, the string is written to a
different table and reset for this next part number in the table. Need
to repeat until all records in the table have been processed.

This sounds like it be one of the few cases where you need an iterative
solution. Yet, then again:

I use ADO in access 2000 to work thru local recordsets, I just can't find anyway to do this in a stored SQL procedure.

Doing this client-side might be just as effecient. So if there is no
compelling reason for doing this in a stored procedure, you may keep the
ADO solution - even if it means that the data needs to do some extra
roundtrips.

Here is a sample of how such a procedure would look like:

DECLARE @partno varchar(10),
@lastpartno varchar(10),
@otherdata varchar(10),
@output varchar(8000),
@err int

DECLARE part_cur CURSOR LOCAL STATIC FOR
SELECT partno, otherdata FROM tbl ORDER BY partno
SELECT @err = @@error
IF @err <> 0 BEGIN DEALLOCATE part_cur RETURN @err END

OPEN part_cur

WHILE 1 = 1
BEGIN
FETCH part_cur INTO @partno, @otherdata
IF @@fetch_status <> 0
BREAK

IF @partno <> coalesce(@lastpartno, '')
BEGIN
IF @lastpartno IS NOT NULL
BEGIN
INSERT othertbl (col1) VALUES (@output)
SELECT @err = @@error IF @err <> 0 BREAK
END
SELECT @lastpartno = @partno, @output = @partno
END

SELECT @output = @output + ', ' + @otherdata
END

DEALLOCATE part_cur

IF @err <> 0
RETURN @err

INSERT othertbl (col1) VALUES (@output)
SELECT @err = @@error IF @err <> 0 RETURN @err


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp