Overview
Cursor functions provide information about the state of a cursor, allow you to manipulate the cursor position, and retrieve values from the result set. These functions are often used in Transact‑SQL scripts that require row‑by‑row processing.
Cursor Functions List
| Function | Purpose | Returns |
|---|---|---|
CURSOR_STATUS |
Returns the status of a cursor variable or cursor name. | int |
FETCH_STATUS |
Indicates the outcome of the last FETCH statement. | int |
@@CURSOR_ROWS |
Number of rows fetched by the last FETCH statement. | int |
@@FETCH_STATUS |
SQL Server system function that returns the status of the last FETCH. | int |
@@ROWCOUNT |
Number of rows affected by the last statement (also useful with cursors). | int |
Examples
Below are common usage patterns for cursor functions.
Example 1 – Checking Cursor Status
DECLARE @MyCursor CURSOR;
DECLARE @Status int;
SET @MyCursor = CURSOR FOR
SELECT ProductID, ProductName FROM dbo.Products;
OPEN @MyCursor;
SET @Status = CURSOR_STATUS('global', '@MyCursor');
IF @Status = 1
PRINT 'Cursor opened and ready.';
ELSE
PRINT 'Cursor could not be opened.';
CLOSE @MyCursor;
DEALLOCATE @MyCursor;Example 2 – Using FETCH_STATUS
DECLARE product_cursor CURSOR FOR
SELECT ProductID, ProductName FROM dbo.Products;
OPEN product_cursor;
FETCH NEXT FROM product_cursor INTO @ID, @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONCAT('ID: ', @ID, ', Name: ', @Name);
FETCH NEXT FROM product_cursor INTO @ID, @Name;
END
CLOSE product_cursor;
DEALLOCATE product_cursor;