SQL Server Cursor Loop: Complete Guide

1 year ago
Benjamin Taylor
2 minutes

In SQL Server, a cursor is a mechanism used to traverse a result set. Cursors allow us to process data in the result set row by row, similar to using loops in a program.

The general steps of using a cursor are as follows:

  1. Declare a cursor: Use the DECLARE CURSOR statement to declare a cursor and specify the result set to be traversed.
  2. Open the cursor: Use the OPEN statement to open the cursor, ready to start traversing the result set.
  3. Retrieve the next row of data: Use the FETCH statement to retrieve the row data at the current position of the cursor and move the cursor to the next row.
  4. Process data: Handle the data obtained from the cursor in the loop.
  5. Close the cursor: Use the CLOSE statement to close the cursor and release resources.

下面是一个简单的示例，演示如何使用游标在一个表中循环遍历每一行数据并输出：

DECLARE @id INT
DECLARE @name VARCHAR(50)

DECLARE cursor_name CURSOR FOR
SELECT id, name
FROM your_table

OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @id, @name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'ID: ' + CONVERT(VARCHAR, @id) + ', Name: ' + @name
    FETCH NEXT FROM cursor_name INTO @id, @name
END

CLOSE cursor_name
DEALLOCATE cursor_name

In the example above, a cursor named cursor_name is first declared, then opened to retrieve the first row of data. Within the loop, each row of data is processed and printed to the console, followed by the retrieval of the next row of data. Finally, the cursor is closed and resources are released.

It is important to be aware that cursors may impact performance in actual applications, so they should be used cautiously. In some cases, modifying the query statement or using alternative methods can be used instead of cursors.

bannerAds