The  Cursor is a Database object which allows us to process each row and manipulate its data. A Cursor is always associated with a Select Query and it will process each row returned by the Select Query one by one.

Using Cursor we can verify each row data, modify it or perform calculations which are not possible when we get all records at once.

A simple example would be a case where you have records of product and you need to calculate price of each product after deducting discount. And you can also use select statement as well as  insert /update/delete  each records one by one by from the table by using Cursor.


Syntax:



DECLARE @variable1
varchar(50), @variable2 varchar(50) 
DECLARE cursorName CURSOR -- Declare cursor
LOCAL SCROLL STATIC
FOR
Select Column1, Column2 FROM myTable
OPEN cursorName -- open the cursor
FETCH NEXT FROM cursorName
   INTO @variable1, @variable2
 
WHILE @@FETCH_STATUS = 0 BEGIN
 
 PRINT @variable1+ ' ' + @variable2 -- print the name
 
FETCH NEXT FROM cursorName
INTO @variable1, @variable2 
END
CLOSE cursorName -- close the cursor
DEALLOCATE cursorName -- Deallocate the cursor

 

Example:

 

 

 SET NOCOUNT ON;
 
 --DECLARE THE VARIABLES FOR HOLDING DATA.
 DECLARE @ProductId INT
 ,@ProductName VARCHAR(100)
 ,@Model VARCHAR(250)
 ,@Brand VARCHAR(250)
 ,@Price decimal(10,2)
 
 --DECLARE AND SET COUNTER.
 DECLARE @Counter INT
 SET @Counter = 1
 
 --DECLARE THE CURSOR FOR A QUERY.
 DECLARE Product_CURSOR CURSOR LOCAL SCROLL STATIC
 FOR
 SELECT productId, ProductName, Model,Brand,Price
 FROM product
 
 --OPEN CURSOR.
 OPEN Product_CURSOR
 
 --FETCH THE RECORD INTO THE VARIABLES.
 FETCH NEXT FROM Product_CURSOR INTO
 @ProductId, @ProductName, @Model,@Brand,@Price
 
 --LOOP UNTIL RECORDS ARE AVAILABLE.
 WHILE @@FETCH_STATUS = 0
 BEGIN
 IF @Counter = 1
 BEGIN
 PRINT 'Product ID' +  CHAR(9)  + 'Product Name' + CHAR(9)  +CHAR(9)+'Model' + CHAR(9) +CHAR(9) +CHAR(9)+ 'Brand' + CHAR(9) +CHAR(9) +CHAR(9) + 'Price'
 PRINT '-------------------------------------------------------------------------------------------------'
 END
 
 --PRINT CURRENT RECORD.
  PRINT CAST(@ProductId AS VARCHAR(10)) + CHAR(9)+  CHAR(9)+ CHAR(9) + @ProductName + CHAR(9) +CHAR(9) +CHAR(9) + @Model+ CHAR(9) +CHAR(9) +CHAR(9) + @Brand  + CHAR(9) +CHAR(9) +CHAR(9) + CAST(@Price AS VARCHAR(10)) 
 
 --INCREMENT COUNTER.
 SET @Counter = @Counter + 1
 
 --FETCH THE NEXT RECORD INTO THE VARIABLES.
 FETCH NEXT FROM Product_CURSOR INTO
 @ProductId, @ProductName, @Model,@Brand,@Price
 END
 
 --CLOSE THE CURSOR.
 CLOSE Product_CURSOR
 DEALLOCATE Product_CURSOR


Output:


CUSROR IN SQL SERVER


SQL Cursor Components


  • DECLARE –Declare variables used in the code block

SELECT–Initialize the variables to a specific value

  • DECLARE CURSOR –Populate the cursor with values that will be evaluated
    • NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement.  This could be 1 or many variables and associated columns.
  • OPEN– Open the cursor to begin data processing
  • FETCH NEXT–Assign the specific values from the cursor to the variables
    • NOTE - This logic is used for the initial population before the WHILE statement and 
        • then again during each loop in the process as a piece of the WHILE statement
  • WHILE - Condition to begin and continue data processing
  • BEGIN...END - The Start and end of the code block
    • NOTE - Based on the data processing multiple BEGIN...END statements can be used
  • CLOSE– its Releases the current data and associated locks, but permits the cursor to be re-opened
  • DEALLOCATE – Destroys the cursor

 

Thanks.

 

comments


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like