SytelineMaster

For those who come from old school of Progress Syteline (Symix) like me, “For Each” command is so nature to us, in terms of loop through record for processing.  But SQL is pretty much bulk processing language.  There is simply no equivalent command as “For Each” in T-SQL. 

There are basically two way to loop through each record in a record set for processing in T-SQL.  The first one, also the most popular one being used through out Syteline SP, is to use the CURSOR. 

Use CURSOR

Here is a example:

DECLARE CUR_ITEMWHSE CURSOR LOCAL STATIC FOR
    SELECT
        item,
        whse,
        qty_on_hand,
        qty_alloc_co
    From itemwhse
    where itemwhse.qty_on_hand > 0 and qty_alloc_co > 0

OPEN CUR_ITEMWHSE
    WHILE @Severity = 0
    BEGIN
        FETCH CUR_ITEMWHSE INTO
            @item,
            @whse,
            @qty_on_hand,
            @qty_alloc_co

        IF @@FETCH_STATUS = -1
            BREAK

        …  /* Your process logic here */

     END

CLOSE CURSOR CUR_ITEMWHSE

Other way is to use the temp table.

Use Temp Table

You will first need to define a temp table with a field “Processed”

declare @Consumers table (
    [OrderID] [int] NOT NULL,
    [OrderDate] [smalldatetime] NULL,
    …   

    [Processed] [smallint] null
primary key (OrderID)
)

You then loop through the record set by select the first 1, which hasn’t been processed.

/* Loop through Consumers  to process     */       
While Exists(Select 1 from @Consumers where processed = 0)
begin
select top 1
    @OrderID = [OrderID],
    @OrderDate = [OrderDate],
     …
      from @Consumers where processed = 0

    … /* Your process logic here  */

Update @Consumers

set processed = 1 where OrderID = @OrderID

end

Be noted that these two method can not be mixed.  You can’t declare cursor for a temp table.