SQL Cursors — Parv The IT Geek

Cursors are a way of manipulating data and interacting with them one at a time. They have a bad reputation within the SQL world as they go against the SET based logic and they can have a very high performance cost. Where possible you should ask yourself whether you could avoid using a Cursor. This is because of the performance advantages a SET based solution has and that Cursor problems only increase when the tasks are scaled up.

The five general steps of a cursor are:

  1. Declaration of the cursor
  2. Opening the cursor
  3. Fetching and manipulating the data
  4. Closing the cursor
  5. Deallocating the cursor

A simple example of a Cursor is below.

Declare @Databases varchar(50)</pre>

Declare DatabasesOnIntance CURSOR READ_ONLY FOR SELECT name FROM sys.databases order by name

Open DatabasesOnIntance

Fetch next from DatabasesOnIntance into @Databases

While @@FETCH_STATUS = 0

Begin

Print @Databases

Fetch next from DatabasesOnIntance into @Databases

End

Close DatabasesOnIntance

Deallocate DatabasesOnIntance

As you can see the Cursor I have created has printed each database on the instance


Originally published at https://parvtheitgeek.com on March 23, 2015.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

<span>%d</span> bloggers like this: