SQL Server cursor usage

Because SQL server does not provide a method to directly traverse each row of the table, it is only replaced by a cursor and a while loop. The cursor can also be used to traverse tables only using a while loop (this method is available when the ID is int and the primary key is used). However, I have not performed any comparison tests in the two methods, I am not sure whether efficiency is high or low.

I only give a simple example of using a cursor. For more information about and using a cursor, see the relevant documentation of msdn. Click here to go directly.

A category table is provided, including the ID, category, parentid, and status fields.

Code:

Declare @ category nvarchar (50)
Declare pcurr cursor
Select category from categories
Open pcurr
Fetch next from pcurr into @ category
While (@ fetch_status = 0)
Begin

Print (@ category)
Fetch next from pcurr into @ category
End
Close pcurr
Deallocate pcurr

 

This sectionCodeIt is easy to traverse the category table and print the name of each category.

There are only two points to mention:

1. The procedure of using a cursor is: defining a cursor, opening a cursor, and closing a cursor.
Declare pcurr cursor... Define a cursor for a result set
Open pcurr
Close pcurr
Deallocate pcurr indicates deleting the cursor reference (this also indicates that the cursor is like the row pointer of the table. The pointer should be destroyed after use)

2. Values of @ fetch_status: 0, 1, and 2
0. The fetch statement is successful. -1 The fetch statement fails or the row is not in the result set. -2 The extracted row does not exist.