Cursor In SQL Server

Hello guys, In this blog, I am going to explain you about the Cursor and how to perform a cursor inside the database.

Cursor-

Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database Server at the Time of Performing DML (Data Manipulation Language) operations on Table by User. Cursors are used to store Database Tables.

Cursor Actions-

•  Declare Cursor

• Open Cursor

• Fetch

• Close

• Deallocate

Types of Cursors-

There are 2 types of Cursors: 

• Explicit cursor

• Implicit cursor

Explicit Cursors-

Explicit Cursors are Created by Users whenever the user requires them. Explicit Cursors are used for Fetching data from Table in Row-By-Row Manner.

Implicit Cursors –

Implicit Cursors are also known as Default Cursors of SQL Server. These Cursors are allocated by SQL Server when the user performs DML operations.

Uses of cursor-

• The main function of the cursor is to retrieve data, one row at a time, from the result set, unlike the SQL commands which operate on all the rows in the result set at one time.

• Cursor are used when the user needs to update records in a single ton fashion or in a row by row manner, in a database table.

Advantages of cursor-

• Cursors can be faster than a while loop but they do have more overhead.

• It is we can do row wise validation or in other way you can perform operation on each row.

• It is a data type which is used to define multi-value variable.

• Cursors can be faster than a while loop but at the cost of more overhead.

 Disadvantages of cursor-

•  It consumes more resources because use-cursor-in-SQL occupies memory from   system that may be available for the other processes. 

•  Each time when a row is fetched from the use-cursor-in-SQL an it may result in a   network round trip.

•  This uses much more network bandwidth than the execution of a single SQL   statements like SELECT or DELETE, etc.  that makes only one round trip.

How to implement cursor inside the database-

Step 1- First you have to select a database and inside the database you have to declare a cursor for the select statement.

Fig.1

Step 2- Then you have to declare a variable.

Fig.2

Step 3- Then you have to open the cursor and fetch the data.

Fig.3

Step 4- After that you have to write a syntax while @@Fetch status =0 if the fetch status is zero that means it successfully fetch and write the business logic.

Fig.4

Step 5-After that again fetch next if table is having more data it will going to check the condition is true or false if it true again it will check.

Fig.5

Step 6- In last fetch status is 1 it will come out from the loop and end and close the cursor and deallocate the cursor.

Fig.6

Step 7- Here the hole statement about the cursor.

Fig.7

Step 8- Here you have to see the particular statement is running and give the result for the cursor inside the database.

Fig.8