MS SQL Basics Tutorial: Application of Cursors

In the previous sections, we detailed how to declare a cursor, read data from a cursor, and close and release a cursor. Below we will give a few examples of application to enable readers to have a more comprehensive understanding of cursors.

Application of 13.7.1 cursor variable

We have mentioned that cursors are new types of variables that are used from the MS SQL SERVER 7 version, and that cursors are often applied in stored procedures, triggers. So how are cursors applied as variables? Look at the system process sp_cursor_list SQL statement text, will be very rewarding.

In the stored procedure, the @cursor_return is a cursor variable and its result set is returned to its caller, which holds the values of the properties for all current cursors.

tip: in stored procedures, if a variable defined as a cursor type uses the OUTPUT option, you must use the varying reserved word, which is the @variable CURSOR varying output form.

Example 13-9: If you are preparing to process data retrieved from the database server in the client program, you can create a stored procedure that contains cursors.

First delete stored procedures with the same name Jobs_cursor

Then create the stored procedure Jobs_cursor

13.7.2 nested cursors

To handle the result set flexibly, you can use a multi-tier cursor in a stored procedure.

Example 13-10: This example creates a stored procedure that contains a two-tier cursor. The first-level cursor holds data records in the authors table that conform to the process parameters, reads a authors data from the first-tier cursor, and then traverses the second-level cursor to read the information about the authors. This is useful if you are preparing to generate a nested report.

See the full set of "MS SQL Basics Tutorials"