What is the use of a cursor in SQL Server

Question

I want to use a database cursor  first I need to understand what its use and syntax are  and in which scenario we can use this in stored procedures  Are there different syntaxes for different versions of SQL Server   When is it necessary to use

User · Answer

Cursor itself is an iterator  like WHILE   By saying iterator I mean a way to traverse the record set  aka a set of selected data rows  and do operations on it while traversing  Operations could be INSERT or DELETE for example  Hence you can use it for data retrieval for example  Cursor works with the rows of the result set sequentially - row by row  A cursor can be viewed as a pointer to one row in a set of rows and can only reference one row at a time  but can move to other rows of the result set as needed   This link can has a clear explanation of its syntax and contains additional information plus examples   Cursors can be used in Sprocs too  They are a shortcut that allow you to use one query to do a task instead of several queries  However  cursors recognize scope and are considered undefined out of the scope of the sproc and their operations execute within a single procedure  A stored procedure cannot open  fetch  or close a cursor that was not declared in the procedure

User · Answer

In SQL server  a cursor is used when you need Instead of the T-SQL commands that operate on all the rows in the result set one at a time  we use a cursor when we need to update records in a database table in a singleton fashion  in other words row by row to fetch one row at a time or row by row    Working with cursors consists of several steps     Declare - Declare is used to define a new cursor  Open - A Cursor is opened and populated by executing the SQL statement defined by the cursor  Fetch - When the cursor is opened  rows can be retrieved from the cursor one by one  Close - After data operations  we should close the cursor explicitly  Deallocate - Finally  we need to delete the cursor definition and release all the system resources associated with the cursor  Syntax  DECLARE cursor name CURSOR   LOCAL   GLOBAL     FORWARD ONLY   SCROLL     STATIC   KEYSET   DYNAMIC   FAST FORWARD     READ ONLY   SCROLL LOCKS   OPTIMISTIC     TYPE WARNING  FOR select statement  FOR UPDATE   OF column name       n

User · Answer

Cursor might used for retrieving data row by row basis its act like a looping statement ie while or for loop   To use cursors in SQL procedures  you need to do the following  1 Declare a cursor that defines a result set  2 Open the cursor to establish the result set  3 Fetch the data into local variables as needed from the cursor  one row at a time  4 Close the cursor when done   for ex   declare  tab table   Game varchar 15   Rollno varchar 15    insert into  tab values  Cricket   R11   insert into  tab values  VollyBall   R12    declare  game  varchar 20  declare  Rollno varchar 20   declare cur2 cursor for select game rollno from  tab   open cur2  fetch next from cur2 into  game  rollno  WHILE     FETCH STATUS   0    begin  print  game  print  rollno  FETCH NEXT FROM cur2 into  game  rollno  end  close cur2  deallocate cur2

User · Answer

cursor are used because in  sub query  we can fetch record row by row    so we use cursor to fetch  records   Example of cursor   DECLARE  eName varchar 50    job varchar 50   DECLARE MynewCursor CURSOR -- Declare cursor name  FOR Select eName  job FROM emp where deptno  10  OPEN MynewCursor -- open the cursor  FETCH NEXT FROM MynewCursor INTO  eName   job  PRINT  eName          job -- print the name  WHILE   FETCH STATUS   0  BEGIN  FETCH NEXT FROM MynewCursor  INTO  ename   job  PRINT  eName         job -- print the name  END  CLOSE MynewCursor  DEALLOCATE MynewCursor   OUTPUT   ROHIT                           PRG   jayesh                          PRG Rocky                           prg Rocky                           prg

User · Answer

I would argue you might want to use a cursor when you want to do comparisons of characteristics that are on different rows of the return set  or if you want to write a different output row format than a standard one in certain cases  Two examples come to mind    One was in a college where each add and drop of a class had its own row in the table  It might have been bad design but you needed to compare across rows to know how many add and drop rows you had in order to determine whether the person was in the class or not  I can t think of a straight forward way to do that with only sql    Another example is writing a journal total line for GL journals  You get an arbitrary number of debits and credits in your journal  you have many journals in your rowset return  and you want to write a journal total line every time you finish a journal to post it into a General Ledger  With a cursor you could tell when you left one journal and started another and have accumulators for your debits and credits and write a journal total line  or table insert  that was different than the debit credit line

User · Answer

Cursors are a mechanism to explicitly enumerate through the rows of a result set  rather than retrieving it as such   However  while they may be more comfortable to use for programmers accustomed to writing While Not RS EOF Do      they are typically a thing to be avoided within SQL Server stored procedures if at all possible -- if you can write a query without the use of cursors  you give the optimizer a much better chance to find a fast way to implement it   In all honesty  I ve never found a realistic use case for a cursor that couldn t be avoided  with the exception of a few administrative tasks such as looping over all indexes in the catalog and rebuilding them   I suppose they might have some uses in report generation or mail merges  but it s probably more efficient to do the cursor-like work in an application that talks to the database  letting the database engine do what it does best -- set manipulation

[sql-server] What is the use of a cursor in SQL Server?

Examples related to sql-server

Examples related to database-cursor