Skip to main content

SQL Server: Cursors - Basics and Example of Cursors

SQL Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis; it’s like recordset in the ASP.
SQL Server Cursor is a row base operator but cursor is not recommended because of performance issue as it create different execution plan for each rows. So one should look out for other i.e. try to implement logic with the help of while loop or CASE statement or JOIN, SELECT, GROUP etc. statements.

But few times the cursor is recommended and useful, for example if you just need to update your DB only once through Query Analyzer and don’t really bothered about performance, then cursor can be handful;

SQL Server Cursor Basics: Before jumping to example, let’s see basics of SQL Cursor statements.

Statement
Description
DECLARE
Declare variables used in the code block
SET\SELECT
Initialize the variables to a specific value
DECLARE CURSOR
Populate the cursor with values that will be evaluated. There are an equal number of variables in the DECLARE <Name_Of_Cursor>
OPEN
Open the cursor to begin data processing
FETCH NEXT
Assign the specific values from the cursor to the variables; This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
WHILE
Condition to begin and continue data processing
BEGIN...END
Start and end of the code block. There can be multiple BEGIN...END statements.
Processing of Data
Any DML statement or query logic you want to process.
CLOSE
Releases the current data and associated locks, but permits the cursor to be re-opened
DEALLOCATE
Destroys the cursor

Cursor Example: For of all let’s create one table; we’ll use this table in our example cursor.

/***** Create Table: CountryTable *****/
CREATE TABLE CountryTable
(
 Id INT PRIMARY KEY,
 Name NVARCHAR(50) NOT NULL
)
GO

/***** INSERT Into: CountryTable *****/
INSERT INTO CountryTable VALUES(1,'India')
INSERT INTO CountryTable VALUES(2,'USA')
INSERT INTO CountryTable VALUES(3,'Australia')
INSERT INTO CountryTable VALUES(4,'Germany')
INSERT INTO CountryTable VALUES(5,'UK')

Now we’ll create a simple cursor that’ll iterate over this table and show all Id and Name of each country.

DECLARE @CountryId INT, @CountryName NVARCHAR(50)
/***** Declare Cursor *****/
DECLARE @GetCountryDetail CURSOR

/***** Set Cursor *****/
SET @GetCountryDetail = CURSOR FOR
SELECT Id, Name FROM CountryTable

/***** Open Cursor *****/
OPEN @GetCountryDetail
      FETCH NEXT
      FROM @GetCountryDetail INTO @CountryId, @CountryName
      WHILE @@FETCH_STATUS = 0
      BEGIN
            /***** Print Result *****/
            PRINT 'Country Id: ' + CAST (@CountryId AS NVARCHAR(10))
                  + ' And Country Name: ' + @CountryName

            FETCH NEXT
            FROM @GetCountryDetail INTO @CountryId, @CountryName
      END

/***** Close Cursor *****/
CLOSE @GetCountryDetail

/***** Deallocate Cursor *****/
DEALLOCATE @GetCountryDetail

Output will be:

Country Id: 1 And Country Name: India
Country Id: 2 And Country Name: USA
Country Id: 3 And Country Name: Australia
Country Id: 4 And Country Name: Germany
Country Id: 5 And Country Name: UK

Few points one should keep in mind regarding cursor:
·         Cursor is an actual object inside the database engine, there is a little overhead involved in creating the cursor and destroying it.
·         Majority of cursor operations occur in tempdb, so a heavily used tempdb will be even more overloaded with the use of cursors.
·         Once cursor is opened we have to close the cursor
·         After the usage cursor should be deallocated from the memory.

Popular posts from this blog

C#: Merging Excel cells with NPOI HSSFWorkbook

In this post we’ll see how to merge the two or more cell with each other while creating the excel sheet using NPOI.
Mentioned below is code to merge multiple cells, in this example we are merging first cell to fifth cell of first row (you can adjust row or cell range by passing particular parameters in CellRangeAddress).
//Created new Workbook var hwb = new NPOI.HSSF.UserModel.HSSFWorkbook(); //Create worksheet with name. var sheet = hwb.CreateSheet("new sheet"); //Create row and cell. var row = sheet.CreateRow(0); var cell = row.CreateCell(0); ; //Set text inside cell cell.SetCellValue("This is Merged cell"); cell.CellStyle.WrapText = true; //define cell range address // parameters: -> first row to last and first cell to last cell var cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4); //Add merged region to sheet. sheet.AddMergedRegion(cra);
Hope this solution helps you J

Read and parse a CSV file into an array of rows and columns in C#

The following PopulateCsvIntoArraymethod used to read the CSV file into a two-dimensional array of strings.
I have included explanation of method lines, wherever needed. ///<summary> /// Populate the CSV file into an array, /// We assume that every line has the same number of fields and there may be blank lines. ///</summary> ///<returns></returns> privatestring[,] PopulateCsvIntoArray() { // Get path of CSV file. var path = Server.MapPath("~/Folder_Name/testfile.csv"); // Get the file's text using ReadAllText method. string fileData = System.IO.File.ReadAllText(path);
// Split CSV data into lines.     fileData = fileData.Replace('\n', '\r'); string[] lines = fileData.Split(newchar[] { '\r' }, StringSplitOptions.RemoveEmptyEntries);
// Get rows and columns counts. int totalRows = lines.Length;

Calculate Sum and average of two or more Textbox values using JQuery

We can calculate sum and average of values entered in textboxes with the help of small piece of jQuery code.
First of all let’s create a HTML form where we’ll ask user to enter marks in each subject, under body section add following HTML:
<tableclass="demo-table"> <trclass="label"> <tdcolspan="2">Please enter marks(0-99):</td> </tr> <tr> <td>English</td> <td> <inputtype="text"class="txtMarks"name="txtMarks"