sql

A cursor is a programming logic mostly used in stored procedures to perform complex logic on a row by row basis. The select statement in a stored procedure only helps you to get all the results but will not keep in memory so that you can work on each row.

A cursor can only work on one row at a time. You cannot do something like a batch updates or insertions using cursors.

To use a cursor, we need to follow the steps:

1. Declare a cursor that defines a resultset
2. Open the cursor variable to establish the resultset.
3. Fetch the data into local variables as needed from cursor, one row at a time.
4. Perform operations on each row.
5. Close the cursor when the operation is completed i.e., all row have been completed.

The following statements are needed to define cursors:
DECLARE CURSOR
OPEN
FETCH
CLOSE
DEALLOCATE

Example of the cursor:

DECLARE @AccountID INT

DECLARE @getAccountID CURSOR

SET @getAccountID = CURSOR FOR

SELECT Account_ID

FROM Accounts

OPEN @getAccountID

FETCH NEXT

FROM @getAccountID INTO @AccountID

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @AccountID

FETCH NEXT

FROM @getAccountID INTO @AccountID

END

CLOSE @getAccountID

DEALLOCATE @getAccountID

Let me know if you have any questions.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Akkiraju Ivaturi14y ago
A cursor is a programming logic mostly used in stored procedures to perform complex logic on a row by row basis. The select statement in a stored procedure only helps you to get all the results but will not keep in memory so that you can work on each row.

A cursor can only work on one row at a time. You cannot do something like a batch updates or insertions using cursors.

To use a cursor, we need to follow the steps:

1. Declare a cursor that defines a resultset
2. Open the cursor variable to establish the resultset.
3. Fetch the data into local variables as needed from cursor, one row at a time.
4. Perform operations on each row.
5. Close the cursor when the operation is completed i.e., all row have been completed.

The following statements are needed to define cursors:
DECLARE CURSOR
OPEN
FETCH
CLOSE
DEALLOCATE

Example of the cursor:

DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts

OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

Let me know if you have any questions.