{"id":157,"date":"2019-09-24T07:56:31","date_gmt":"2019-09-24T07:56:31","guid":{"rendered":"http:\/\/sumitjangid.com\/?p=157"},"modified":"2019-09-24T08:03:46","modified_gmt":"2019-09-24T08:03:46","slug":"cursor","status":"publish","type":"post","link":"http:\/\/sumitjangid.com\/index.php\/2019\/09\/24\/cursor\/","title":{"rendered":"Cursor"},"content":{"rendered":"\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<\/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<\/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\n
12<\/td>DECLARE cursor_name CURSOR    FOR select_statement;<\/td><\/tr><\/tbody><\/table>\n\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

Next, open and popular the cursor by executing the SELECT<\/code> statement:<\/p>\n\n\n\n
1<\/td>OPEN cursor_name;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Then, fetch a row from the cursor into one or more variables<\/a>:<\/p>\n\n\n\n
1<\/td>FETCH NEXT FROM cursor INTO variable_list;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

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\n
1234<\/td>WHILE @@FETCH_STATUS = 0      BEGIN        FETCH NEXT FROM cursor_name;      END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

After that, close the cursor:<\/p>\n\n\n\n
1<\/td>CLOSE cursor_name;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Finally, deallocate the cursor:<\/p>\n\n\n\n
1<\/td>DEALLOCATE cursor_name;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

SQL Server cursor example<\/h2>\n\n\n\n

First, declare two variables to hold product name and list price, and a cursor to hold the result of a query that selects product name and list price from the production.products<\/code> table:<\/p>\n\n\n\n
12345678910<\/td>DECLARE     @product_name VARCHAR(MAX),     @list_price   DECIMAL; DECLARE cursor_product CURSORFOR SELECT         product_name,         list_price    FROM         production.products;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Next, open the cursor:<\/p>\n\n\n\n
1<\/td>OPEN cursor_product;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Then, fetch each row from the cursor and print out the product name and list price:<\/p>\n\n\n\n
1234567891011<\/td>FETCH NEXT FROM cursor_product INTO     @product_name,     @list_price; WHILE @@FETCH_STATUS = 0    BEGIN        PRINT @product_name + CAST(@list_price AS varchar);        FETCH NEXT FROM cursor_product INTO             @product_name,             @list_price;    END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

After that, close the cursor.<\/p>\n\n\n\n
1<\/td>CLOSE cursor_product;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Finally, deallocate the cursor to release it.<\/p>\n\n\n\n
1<\/td>DEALLOCATE cursor_product;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

The following code snippets put everything together:<\/p>\n\n\n\n
12345678910111213141516171819202122232425262728<\/td>DECLARE     @product_name VARCHAR(MAX),     @list_price   DECIMAL; DECLARE cursor_product CURSORFOR SELECT         product_name,         list_price    FROM         production.products; OPEN cursor_product; FETCH NEXT FROM cursor_product INTO     @product_name,     @list_price; WHILE @@FETCH_STATUS = 0    BEGIN        PRINT @product_name + CAST(@list_price AS varchar);        FETCH NEXT FROM cursor_product INTO             @product_name,             @list_price;    END; CLOSE cursor_product; DEALLOCATE cursor_product;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Here is the partial output:<\/p>\n\n\n\n

\"SQL<\/figure>\n","protected":false},"excerpt":{"rendered":"

SQL works based on set e.g., SELECT 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… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2,18,19],"tags":[],"_links":{"self":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/157"}],"collection":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/comments?post=157"}],"version-history":[{"count":1,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/157\/revisions"}],"predecessor-version":[{"id":158,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/157\/revisions\/158"}],"wp:attachment":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/media?parent=157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/categories?post=157"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/tags?post=157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}