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

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"

Difference between Web API, WCF and Web Service

So now we have got the basic idea about Web API, now let’s do some comparison of Web API with WCF and web services.
Web Service WCF Web API Web services created as file with .asmx extension. WCF create with .svc extension Web API are simple class file with .cs(for C#) extension. Web API is inherited from “ApiController” and class name must end with “Controller”. It is SOAP based service and return data in XML form. It is also based on SOAP and return data in XML form. Web API is HTTP based service and by default it return data in JSON or XML form. It supports only HTTP protocol. It supports various protocols like TCP, HTTP, HTTPS, Named Pipes, and MSMQ. It supports HTTP protocol. It can be hosted only on IIS. It can be hosted with in the application or on IIS or using window service. It can be hosted with in the application or on IIS. It is not open source but can be consumed by any client that understands xml. It is not open source but can be consumed by any client t…