Basic Cursors in T-SQL–#SQLNewBlogger from Blog Posts – SQLServerCentral

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Cursors are not efficient, and not recommended for use in SQL Server/T-SQL. This is different from other platforms, so be sure you know how things work.

There are places where cursors are useful, especially in one-off type situations. I recently had a situation, and typed “CREATE CURSOR”, which resulted in an error. This isn’t valid syntax, so I decided to write a quick post to remind myself what is valid.

The Basic Syntax

Instead of CREATE, a cursor uses DECLARE. The structure is unlike other DDL statements, which are action type name, as CREATE TABLE dbo.MyTable. Instead we have this:

DECLARE cursorname CURSOR

as in

DECLARE myCursor CURSOR

There is more that is needed here. This is just the opening. The rest of the structure is

DECLARE cursorname CURSOR [options] FOR select_statement

You can see this in the docs, but essentially what we are doing is loading the result of a select statement into an object that we can then process row by row. We give the object a name and structure this with the DECLARE CURSOR FOR.

I was recently working on the Advent of Code and Day 4 asks for some processing across  rows. As a result, I decided to try a cursor like this:

DECLARE pcurs CURSOR FOR SELECT lineval FROM day4 ORDER BY linekey;

The next steps are to now process the data in the cursor. We do this by fetching data from the cursor as required. I’ll build up the structure here starting with some housekeeping.

In order to use the cursor, we need to open it. It’s good practice to then deallocate the objet at the end, so let’s set up this code:

DECLARE pcurs CURSOR FOR SELECT lineval FROM day4 ORDER BY linekey;
OPEN pcurs
...
DEALLOCATE pcurs

This gets us a clean structure if the code is re-run multiple times. Now, after the cursor is open, we fetch data from the cursor. Each column in the SELECT statement can be fetched from the cursor into a variable. Therefore, we also need to declare a variable.

DECLARE pcurs CURSOR FOR SELECT lineval FROM day4 ORDER BY linekey;
OPEN pcurs
DECLARE @val varchar(1000);
FETCH NEXT FROM pcurs into @val
...
DEALLOCATE pcurs

Usually we want to process all rows, so we loop through them. I’ll add a WHILE loop, and use the @@FETCH_STATUS variable. If this is 0, there are still rows in the cursor. If I hit the end of the cursor, a –1 is returned.

DECLARE pcurs CURSOR FOR SELECT lineval FROM day4 ORDER BY linekey;
OPEN pcurs
DECLARE @val varchar(1000);
FETCH NEXT FROM pcurs into @val
WHILE @@FETCH_STATUS = 0
  BEGIN
   ...
   FETCH NEXT FROM pcurs into @val
   END
DEALLOCATE pcurs

Where the ellipsis is is where I can do other work, process the value, change it, anything I want to do in T-SQL. I do need to remember to get the next row in the loop.

As I mentioned, cursors aren’t efficient and you should avoid them, but there are times when row processing is needed, and a cursor is a good solution to understand.

SQLNewBlogger

As soon as I realized my mistake in setting up the cursor, I knew some of my knowledge had deteriorated. I decided to take a few minutes and describe cursors and document syntax, mostly for myself.

However, this is a way to show why you know something might not be used. You could write a post on replacing a cursor with a set based solution, or even show where performance is poor from a cursor.

The post Basic Cursors in T-SQL–#SQLNewBlogger appeared first on SQLServerCentral.