Lets you add row numbering to your output. It may not be an ideal solution (temp table with an identity column would probably be more efficient) but it shows cursors and some other little coding techniques. Enjoy!


--**************************************
-- Name: RowCounterCursor
-- Description:Lets you add row numbering to your output. It may not be an ideal solution (temp table with an identity column would probably be more efficient) but it shows cursors and some other little coding techniques.
Enjoy!
-- By: Greg
--
--
-- Inputs:None
--
-- Returns:None
--
--Assumes:Uses the Northwind dB
--
--Side Effects:None
--This code is copyrighted and has limited warranties.
--Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.252/lngWId.5/qx/vb/scripts/ShowCode.htm
--for details.
--**************************************

/*
Author: Greg
Title: RowCounterCursor
Date: December 8, 2000
It was 20 years ago today...
*/
USE Northwind
-- Set up variables we'll use later
DECLARE @A INT,
@Zeroes INT,
@RowID CHAR(20),
@OrdID CHAR(10),
@ShipTo CHAR(20),
@Cust CHAR(20),
@Message CHAR(100)
set @A=1 -- initialize our row counter
-- set up a "Header" row
set @Message = 'Row Order IDShip to City Customer'
PRINT @Message
set @Message = '----------------------------------------------------'
PRINT @Message
-- Create a cursor
DECLARE Counter CURSOR FOR
SELECT OrderID, ShipCity, CustomerID
FROM orders
OPEN Counter
FETCH NEXT FROM Counter INTO @OrdID, @ShipTo, @Cust

WHILE @@FETCH_STATUS = 0


BEGIN
-- formatting the row to begin with zeroes, not necessary but a nice touch
SET @Zeroes = STR(LEN(@A))
SELECT @Zeroes =
CASE @Zeroes
WHEN '1' THEN '2'
WHEN '2' THEN '1'
ELSE '0'
END
SET @Message = REPLICATE ('0', @Zeroes) + CAST(@A AS CHAR(5)) + SPACE(4 - @Zeroes) + @OrdID + ' ' + @ShipTo + ' ' + @Cust
PRINT @Message
FETCH NEXT FROM Counter INTO @OrdID, @ShipTo, @Cust
SET @A = @A + 1
END
-- clean up after ourseves
CLOSE Counter
DEALLOCATE Counter