Using Cursors When you execute a SELECT statement, all the rows are returned in one go. This might not always be appropriate. For example, you might want to take some action based on the column values retrieved for a particular row | Using Cursors When you execute a SELECT statement all the rows are returned in one go. This might not always be appropriate. For example you might want to take some action based on the column values retrieved for a particular row. To do this you can use a cursor to process rows retrieved from the database one row at a time. A cursor allows you to step through the rows returned by a particular SELECT statement. You follow these steps when using a cursor 1. Declare variables to store the column values from the SELECT statement. 2. Declare the cursor specifying your SELECT statement. 3. Open your cursor. 4. Fetch the rows from your cursor. 5. Close your cursor. You ll learn the details of these steps in the following sections. Step 1 Declare Variables to Store the Column Values from the SELECT Statement These variables must be compatible with the column types for the retrieved rows. For example you ll want to use an int variable to store the value from an int column and so on. The following example declares three variables to store the ProductID ProductName and UnitPrice columns from the Products table DECLARE @MyProductID int DECLARE @MyProductName nvarchar 40 DECLARE @MyUnitPrice money Step 2 Declare the Cursor A cursor declaration consists of a name that you assign to the cursor and the SELECT statement that you want to execute. This SELECT statement is not actually run until you open the cursor. You declare your cursor using the DECLARE statement. The following example declares a cursor named ProductCursor with a SELECT statement that retrieves the ProductID ProductName and UnitPrice columns for the first 10 products from the Products table DECLARE ProductCursor CURSOR FOR SELECT ProductID ProductName UnitPrice FROM Products WHERE ProductID 10 Step 3 Open the Cursor Now it s time to open your cursor which runs the SELECT statement previously defined in the DECLARE statement. You open a cursor using the OPEN statement. The following example opens ProductCursor and
