Post
Ask Question

Use Cursor In SQL

What is use-cursor-in-sql?

Cursor is a database object to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use use-cursor-in-sql when we need to update records in a database table in singleton fashion means row by row.
Cursor work same as looping concept. A use-cursor-in-sql is a set of rows together with a pointer that identifies a current row.

Why and when to use Cursor?

There are some conditions when we want to get record from one table and need to insert into another with performing some logic or some conditions basically use-cursor-in-sql works as for/while loop.

Advantages
  • Cursors can be faster than a while loop but they do have more overhead.
  • It is we can do RowWise 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

  • It consumes more resources because use-cursor-in-sql occupies memory from system that may be available for other processes.
  • Each time when a row is fetched from the use-cursor-in-sql and it may result in a network round trip. This uses much more network bandwidth than the execution of a single SQL statement like SELECT or DELETE, etc that makes only one round trip.

Syntax for use-cursor-in-sql

  1. DECLARE @fName varchar(50), @lName varchar(50)  
  2. DECLARE use-cursor-in-sqlName CURSOR--Declare use-cursor-in-sql  
  3. LOCAL SCROLL STATIC  
  4. FOR  
  5. Select columnname1, columnname2 FROM myTable  
  6. OPEN use-cursor-in-sqlName--open the use-cursor-in-sql  
  7. FETCH NEXT FROM use-cursor-in-sqlName  
  8. INTO @columnname1, @columnname2  
  9. WHILE @ @FETCH_STATUS = 0  
  10. BEGIN  
  11.   
  12. --here your code or logic like(insert / update / delete)  
  13.   
  14. FETCH NEXT FROM use-cursor-in-sqlName  
  15. INTO @columnname1, @columnname2  
  16. END  
  17. CLOSE use-cursor-in-sqlName--close the use-cursor-in-sql  
  18. DEALLOCATE use-cursor-in-sqlName--Deallocate the use-cursor-in-sql  
Example

Step 1: Create Table and View Table

table

Step 2: Using Cursor

cursor

Step 3: See Update Table Data

table

Note: If you face any difficulty of use-cursor-in-sql please comment. 


Similar Articles