Please disable your adblock and script blockers to view this page
 

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." 
Previous
Next Post »

2 comments

Click here for comments
Anonymous
admin
November 29, 2013 ×

nice article .. keep it up ..

Reply
avatar
November 29, 2013 ×

Thanks for appreciating my work ..keep reading and stay tuned for more updates like this..:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon