SQL Tips #16 - Looping Through Records using Cursors

Cursors let you step through a result set row by row - here's a simple worked example.

The Query

declare @CustId nchar(5)
declare @RowNum int
declare myRecords cursor for
select top 5 CustomerID from Northwind.dbo.Customers
OPEN myRecords
FETCH NEXT FROM myRecords INTO @CustId
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
    set @RowNum = @RowNum + 1
    print cast(@RowNum as char(1)) + ' ' + @CustId
    FETCH NEXT FROM myRecords INTO @CustId
END
CLOSE myRecords
DEALLOCATE myRecords

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags