How do I write "proper" cursors? - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

How do I write "proper" cursors?

sql-server add tag

Hannah Vernon

When writing a cursor in T-SQL, I typically use the following template:

```
DECLARE @var varchar(10);
DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC
FOR 
SELECT gender
FROM (VALUES ('what'), ('is'), ('gender'), ('anyway'))v(gender);

OPEN cur;
FETCH NEXT FROM cur INTO @var;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @var;
	FETCH NEXT FROM cur INTO @var;
END;
CLOSE cur;
DEALLOCATE cur;
```

For the most part this always work well, and isn't all *that* tragic, however [Microsoft Docs](https://docs.microsoft.com/en-us/sql/t-sql/functions/cursor-status-transact-sql?view=sql-server-ver15) shows that `CURSOR_STATUS` can be used to check the status of the cursor.  How is that different from `@@FETCH_STATUS` and how should `@@CURSOR_ROWS` be used to write effective cursor code that works all the time, ala Defensive Programming?

Also, is there anything else that should be done in cursor code to appease [Urutengangana](https://en.wikipedia.org/wiki/Urutengangana)?

Top Answer

Hannah Vernon

`CURSOR_STATUS` provides a nice way of ensuring the cursor is ready and contains rows.

The following code shows a way to check the status of the cursor prior to cursing through the potential resultset:

```
DECLARE @var varchar(10);
DECLARE @cursorStatus smallint;
DECLARE @cursorRows bigint;
DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC
FOR 
SELECT gender
FROM (VALUES ('what'), ('is'), ('gender'), ('anyway'))v(gender);

OPEN cur;
SET @cursorStatus = CURSOR_STATUS('local', 'cur');
SET @cursorRows = @@CURSOR_ROWS;
PRINT 'Cursor contains ' + CONVERT(varchar(30), @cursorRows) + ' rows.';
IF @cursorStatus = 1
BEGIN
    FETCH NEXT FROM cur INTO @var;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @var;
	    FETCH NEXT FROM cur INTO @var;
    END;
END
ELSE
BEGIN
    DECLARE @msg varchar(1000) = 'Cursor not open, or has no rows.
CURSOR_STATUS returned: ';
    SET @msg = @msg + CONVERT(varchar(10), @cursorStatus);
    RAISERROR (@msg, 10, 1) WITH NOWAIT;
END
CLOSE cur;
DEALLOCATE cur;
```

You'll also notice that I've implemented `@@CURSOR_ROWS` to show the number of rows the cursor returned.

1 Answer