{"id":1730,"date":"2019-04-15T12:50:09","date_gmt":"2019-04-15T05:50:09","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1730"},"modified":"2020-04-11T20:12:28","modified_gmt":"2020-04-11T13:12:28","slug":"sql-server-cursor","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-cursor\/","title":{"rendered":"SQL Server CURSOR"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server cursor to process a result set, one row at a time.<\/p>\n\n\n\n

SQL works based on set e.g., SELECT<\/a><\/code> statement returns a set of rows which is called a result set. However, sometimes, you may want to process a data set on a row by row basis. This is where cursors come into play.<\/p>\n\n\n\n

What is a database cursor #<\/a><\/h2>\n\n\n\n

A database cursor is an object that enables traversal over the rows of a result set. It allows you to process individual row returned by a query.<\/p>\n\n\n\n

SQL Server cursor life cycle #<\/a><\/h2>\n\n\n\n

These are steps for using a cursor:<\/p>\n\n\n\n

\"SQL<\/figure>\n\n\n\n

First, declare a cursor.<\/p>\n\n\n

DECLARE<\/span> cursor_name CURSOR<\/span>\n    FOR<\/span> select_statement;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, open and populate the cursor by executing the SELECT<\/code> statement:<\/p>\n\n\n
OPEN cursor_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
FETCH NEXT FROM cursor INTO variable_list;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
WHILE @@FETCH_STATUS = 0  \n    BEGIN<\/span>\n        FETCH<\/span> NEXT<\/span> FROM<\/span> cursor_name;  \n    END<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, close the cursor:<\/p>\n\n\n
CLOSE cursor_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, deallocate the cursor:<\/p>\n\n\n
DEALLOCATE<\/span> cursor_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server cursor example #<\/a><\/h2>\n\n\n\n
We’ll use the prodution.products<\/code> table from the sample database<\/a> to show you how to use a cursor:<\/p>\n\n\n\n
\"products\"<\/figure>\n\n\n\n
First, declare two variables<\/a> to hold product name and list price, and a cursor to hold the result of a query that retrieves product name and list price from the production.products<\/code> table:<\/p>\n\n\n
DECLARE<\/span> \n    @product_name VARCHAR<\/span>(MAX<\/span>), \n    @list_price   DECIMAL<\/span>;\n\nDECLARE<\/span> cursor_product CURSOR<\/span>\nFOR<\/span> SELECT<\/span> \n        product_name, \n        list_price\n    FROM<\/span> \n        production.products;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, open the cursor:<\/p>\n\n\n
OPEN cursor_product;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, fetch each row from the cursor and print out the product name and list price:<\/p>\n\n\n
FETCH NEXT FROM cursor_product INTO \n    @product_name, \n    @list_price;\n\nWHILE @@FETCH_STATUS = 0\n    BEGIN<\/span>\n        PRINT @product_name + CAST<\/span>(@list_price AS<\/span> varchar<\/span>);\n        FETCH NEXT FROM cursor_product INTO \n            @product_name, \n            @list_price;\n    END<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, close the cursor:<\/p>\n\n\n
CLOSE cursor_product;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, deallocate the cursor to release it.<\/p>\n\n\n
DEALLOCATE<\/span> cursor_product;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following code snippets put everything together:<\/p>\n\n\n
DECLARE<\/span> \n    @product_name VARCHAR<\/span>(MAX<\/span>), \n    @list_price   DECIMAL<\/span>;\n\nDECLARE<\/span> cursor_product CURSOR<\/span>\nFOR<\/span> SELECT<\/span> \n        product_name, \n        list_price\n    FROM<\/span> \n        production.products;\n\nOPEN cursor_product;\n\nFETCH NEXT FROM cursor_product INTO \n    @product_name, \n    @list_price;\n\nWHILE @@FETCH_STATUS = 0\n    BEGIN<\/span>\n        PRINT @product_name + CAST<\/span>(@list_price AS<\/span> varchar<\/span>);\n        FETCH NEXT FROM cursor_product INTO \n            @product_name, \n            @list_price;\n    END<\/span>;\n\nCLOSE cursor_product;\n\nDEALLOCATE<\/span> cursor_product;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the partial output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In practice, you will rarely use the cursor to process a result set in a row-by-row manner.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server cursor to process a result set, each row at a time.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
SQL Server provides the @@FETCHSTATUS<\/code> function that returns the status of the last cursor FETCH<\/code> statement executed against the cursor; If @@FETCHSTATUS<\/code> returns 0, meaning the FETCH<\/code> statement was successful. You can use the WHILE<\/a><\/code> statement to fetch all rows from the cursor as shown in the following code:<\/p>\n\n\n
Then, fetch a row from the cursor into one or more variables<\/a>:<\/p>\n\n\n
To declare a cursor, you specify its name after the DECLARE<\/code> keyword with the CURSOR<\/code> data type and provide a SELECT<\/a><\/code> statement that defines the result set for the cursor.<\/p>\n\n\n\n