What is the use of a cursor in SQL Server?

I want to use a database cursor; first I need to understand what its use and syntax 开发者_JS百科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?

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.

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

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

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.

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:

1. 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.

2. 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.

CREATE PROCEDURE [dbo].[SP_Data_newUsingCursor]
(
    @SCode NVARCHAR(MAX)=NULL,
    @Month INT=NULL,
    @Year INT=NULL,
    @Msg NVARCHAR(MAX)=null OUTPUT
)
AS

BEGIN
 
    DECLARE @SEPERATOR as VARCHAR(1)
    DECLARE @SP INT
    DECLARE @VALUE VARCHAR(MAX)
    SET @SEPERATOR = ','
    
    CREATE TABLE #TempSiteCode (id int NOT NULL)
    
    WHILE PATINDEX('%' + @SEPERATOR + '%', @SCode ) <> 0 
        BEGIN
                SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%' ,@SCode)
                SELECT  @VALUE = LEFT(@SCode , @SP - 1)
                SELECT  @SCode = STUFF(@SCode, 1, @SP, '')  
                INSERT INTO #TempSiteCode (id) VALUES (@VALUE)
        END


DECLARE 
@EmpCode bigint=null,
@EmpName nvarchar(50)=null

CREATE TABLE #TempEmpDetail
(
    EmpCode bigint
)


CREATE TABLE #TempFinalDetail
(   
    EmpCode bigint,
    EmpName nvarchar(500)
    
    
)


DECLARE @TempSCursor CURSOR
DECLARE @TempFinalCursor CURSOR



INSERT INTO #TempEmpDetail
(
    EmpCode
)
(

SELECT DISTINCT EmpCode FRom tbl_Att_MSCode
WHERE tbl_Att_MSCode.SiteCode IN (SELECT id FROM #TempSiteCode)
AND fldMonth=@Month AND fldYear=@Year

)

SET @TempSiteFinalCursor=CURSOR FOR SELECT EmpCode FROM #TempEmpDetail
OPEN @TempSiteFinalCursor
FETCH NEXT FROM @TempSiteFinalCursor INTO @EmpCode,@SiteCode,@HrdCompanyId

WHILE @@FETCH_STATUS=0
    BEGIN
        
        SEt @EmpName=(SELECt EmpName FROm tbl_Employees WHERE EmpCode=@EmpCode)

        INSERT INTO #TempFinalDetail
        (       
            EmpCode,
            EmpName
        )

        VALUES
        (
         
          @EmpCode,
          @EmpName
         )


         FETCH NEXT FROM @TempSiteFinalCursor INTO @EmpCode
    END


    SELECT  EmpCode,
            EmpName
            FROM #TempFinalDetail


DEALLOCATE @TempSiteFinalCursor

DROP TABLE #TempEmpDetail
DROP TABLE #TempFinalDetail

END