What is exact use of cursor in sql server

I know this may be repeat question, but I want quick reply, don’t want to search more … well….Thanks for reply in advance ….

I want to write the cursor, I want to understand what is its use, syntax and in which scenario we can use this in stored procedure? What are different syntax for sql server 2008, sql server 2008 R2 and sql server 2005 ?

  • How to generate auto increment field in select query
  • Best way to catch sql unique constraint violations in c# during inserts
  • Client with IP addres is not allowed to access the server Azuredb
  • SQL Server Triggers - grouping by transactions
  • Error using Merge Replication to SQL Compact 3.5
  • T-SQL Query Gives Different Results when declaring integer vs calculating in-query
  • Why its necessary to use ?

    3 Solutions collect form web for “What is exact use of cursor in sql server”

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.