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.
Voice of the DBA 