Use of Cursor in SQL SERVER with simple example to print calculated records

Introduction: In this article i am going to explain what is cursor in Sql server and a basic cursor example to print the calculated records. In related article i explained Advantages and disadvantages of Cursor ,when to use and example to explain

What is Cursor?

Description: Cursor in SQL is temporary work area created in the system memory when a SQL statement is executed that allow us to retrieve data from a result set in row by row fashion and used when we want to update records in a database table one row at a time. So cursor is used to perform complex logic on row by row basis.

A cursor can be viewed as a pointer to one row in a set of rows. Although it can hold more than one row (set of rows the cursor holds is referred to as the active set) but can reference only one row at a time and move to other rows of the result set whenever required.

We can fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language.

Following steps are required to use cursors in SQL procedures 

• Declare a cursor that defines a result set.
• Open the cursor to establish the result set.
• Fetch the data from cursor row by row into local variables as needed
• Close the cursor when data manipulation is completed
• Deallocate the cursor so that all the occupied resources are released.

 So to work with cursors we must use the following 5 SQL statements:

1. DECLARE CURSOR
2. OPEN
3. FETCH
4. CLOSE
5. DEALLOCATE

 Below is the Cursor’s Example with its Syntax.

Syntax to Declare Cursor

Step 1: DECLARE curBookDetails CURSOR STATIC FOR

Note:  curBookDetails is the name of the cursor

Syntax to Open Cursor

A Cursor can be opened locally or globally. If not specified, it is opened locally by default. The syntax to open cursor is given below:

Step 2: OPEN curBookDetails

Syntax to Fetch Cursor

Fetch statement provides many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:

Step 3: FETCH NEXT FROM curBookDetails INTO @name,@auth,@prc

Syntax to Close Cursor

Close statement closes the cursor explicitly. Syntax to close the cursor is given below:

Step 4: CLOSE curBookDetails

Note: It is possible to reopen the cursor once closed.

Syntax to Deallocate Cursor

Deallocate statement deletes the cursor and all the system resources occupied by the cursor are released. The basic syntax to close cursor is given below:

Step 5: DEALLOCATE curBookDetails

Note: It is not possible to reopen the cursor once deallocation

Implementation: Let's create the cursor to check it's working.

• First of all create a table in Sql server with the script mentioned below:

 CREATE TABLE BookDetails

(

                [BookId] [int] IDENTITY(1,1) NOT NULL,

                [BookName] [varchar](100) NULL,

                [Author] [varchar](100) NULL,

                [Publisher] [varchar](200) NULL,

                [Price] [decimal](18, 2) NULL

) 

• Then insert some records in the BookDetails table.

Why to use Cursor?

Suppose we want to print the Book Name, its Author, Price and the discount(10% of price) and the new Calculated Discounted Price. So we will create a cursor that will fetch the records one by one and calculate the discount and the discounted price and print the details.

• Let's create a simple cursor

CREATE PROCEDURE PrintBookDetails_Sp

AS

BEGIN

                SET NOCOUNT ON        

                DECLARE @name varchar(100)

                DECLARE @auth varchar(100)

                DECLARE @prc int

               

                DECLARE curBookDetails CURSOR

                STATIC FOR

                SELECT BookName,Author,Price from BookDetails

                OPEN curBookDetails

               

                IF @@CURSOR_ROWS > 0

                 BEGIN

                                FETCH NEXT FROM curBookDetails INTO @name,@auth,@prc

                 WHILE @@Fetch_status = 0

                 BEGIN                

                                PRINT 'Book Name : ' + @name + ', Author : ' + @auth + ' , Price : ' + convert(varchar(10),@prc) + ', Discount : ' + convert(varchar(10),(@prc*10)/100) + ', Discounted Price : ' + convert(varchar(10),(@prc-(@prc*10)/100))                          

                               

                                FETCH NEXT FROM curBookDetails INTO @name,@auth,@prc

                 END

                END

                               

                CLOSE curBookDetails

                DEALLOCATE curBookDetails

                SET NOCOUNT OFF

END 

• To check the results, execute the stored procedure using the command mentioned below:

exec PrintBookDetails_Sp

It will print the Book details as shown in image below:

Click on image to enlarge

Note:  @@Fetch_status: is a transact-SQL statement that returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection. It returns the Boolean value 0 if the Fetch statement was successful and returns -1 if the FETCH statement failed or the row was beyond the result set and will return -2 if the row fetched is missing. For more details read the @@FETCH_STATUS details 

Note: @@CURSOR_ROWS: is a transact-SQL statement that returns the number of qualifying rows currently in the last cursor opened on the connection. For more details read the @@CURSOR_ROWS details

Now over to you:

" I hope you have got what is Cursor in Sql Server with the example and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 

• Tweet
• Share
• Share
• Share

Related Post