Using Cursors with Microsoft SQL Server

Posted September 19th, 2008 in Microsoft SQL Server

Just about everything I've ever read about cursors for Microsoft SQL Server say not to use them for a variety of reasons. There are some cases where using a cursor is a lot simpler or faster etc than using another method and so I've decided to quickly document an examaple of a SQL Server cursor here for my own reference.

A database cursor is a way of traversing data from a select query within SQL. With a database server like Microsoft SQL Server you would normally run a cursor within a stored procedure. Anway, without further ado, here is my example:

DECLARE
    @id int,
    @foo varchar(100),
    @bar varchar(100)

DECLARE mycursor CURSOR READ_ONLY
FOR SELECT id, foo, bar
FROM foobar
WHERE somefield = 'somevalue'

OPEN mycursor

FETCH NEXT FROM mycursor
INTO @id, @foo, @bar

WHILE @@FETCH_STATUS = 0
BEGIN

    ... do something ...

    FETCH NEXT FROM mycursor
    INTO @id, @foo, @bar

END

CLOSE mycursor
DEALLOCATE mycursor

I'm not going to explain further what each step in the process does - this is really just a quick reference example layout. You can easily modify it to suit your data and table structure.

Share or Bookmark

Share or Bookmark this page using the following services. You will need to have an account with the selected service in order to post links or bookmark this page.

Subscribe or Follow

Subscribe via RSS or email, or follow me on Facebook or Twitter below. The RSS icon takes you through to Feedburner where you can select the service or application to use.