Loop thru a SQL Table in stored proc?

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.Thanks Erland, that worked in the procedure.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93BDF3734C7DEYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Eric Martin (ermartin01@.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, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql