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]

Re: Loop thru a SQL Table in stored proc?

Thanks Erland, that worked in the procedure.

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns93BDF37 34C7DEYazorman@ 127.0.0.1...[color=blue]
> [posted and mailed, please reply in news]
>
> Eric Martin (ermartin01@cox .net) writes:[color=green]
> > Does anyone know of a way to loop thru a SQL table using code in a[/color][/color]
stored[color=blue][color=green]
> > procedure?[/color]
>
> Most people here know that you should not do this kind of thing, but
> one should always strive for set-based solutions. Then again...
>[color=green]
> > 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.[/color]
>
> This sounds like it be one of the few cases where you need an iterative
> solution. Yet, then again:
>[color=green]
> > I use ADO in access 2000 to work thru local recordsets, I just can't[/color][/color]
find[color=blue][color=green]
> > anyway to do this in a stored SQL procedure.[/color]
>
> 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(@lastp artno, '')
> 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/techinf...2000/books.asp[/color]