SQL:Cursors

Refer the Source Video: SQL - Cursors

Cursor works on single record at a time.Performance hit:It works slowly than the SET based operation.
*********************************************************************************
                                  Simple Cursor statements
*********************************************************************************
********
Declaring a Cursor
********
--Declaring a cursor

DECLARE FilmCursor CURSOR

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
********
Opening and Closing Cursors
********
OPEN FilmCursor

--Do something useful here

FETCH NEXT FROM FilmCursor

WHILE @@FETCH_STATUS = 0

FETCH NEXT FROM FilmCursor

CLOSE FilmCursor

DEALLOCATE FilmCursor

*********************************************************************************
                                                Calling a Stored Procedure
*********************************************************************************
******** Executing a Stored Procedure with a Cursor: ********

CREATE PROC spListCharacters ( @FilmID INT ,@FilmName VARCHAR(MAX) ,@FilmDate DATETIME) AS BEGIN PRINT @Name + 'released on' + CONVERT(CHAR(10),@Date,103) PRINT '=================================================' PRINT 'List of Characters' SELECT CastCharacterName FROM CastFilmID=@FilmID END ------- Execute it, so it will create it.
---Below Cursor definition
******** Reading Values into Variables: ******** DECLARE @ID INT DECLARE @Name VARCHAR(MAX) DECLARE @Date DATETIME

--Declaring a cursor
DECLARE FilmCursor CURSOR

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

OPEN FilmCursor

--Do something useful here

FETCH NEXT FROM FilmCursor  INTO @ID,@Name,@Date

WHILE @@FETCH_STATUS = 0

    BEGIN
EXEC spListCharacters @ID,@Name,@Date

FETCH NEXT FROM FilmCursor  INTO @ID,@Name,@Date


END

CLOSE FilmCursor

DEALLOCATE FilmCursor

*********************************************************************************

                                                  Types of Cursors

                                 SQL Server Different Types of Cursors

                   Understanding SQL Server Fast_Forward Server Cursors


*********************************************************************************
********
OTHER FETCH OPTIONS for Cursors: DECLARE FilmCursor CURSOR SCROLL/FORWARD_ONLY
*******

--Do something useful here


FETCH FIRST/LAST FROM FilmCursor
~~~~~~~~
FETCH ABSOLUTE 10 FROM FilmCursor
--ABSOLUTE - 10 (for cursor to move backward).

WHILE @@FETCH_STATUS = 0

FETCH RELATIVE 10 FROM FilmCursor
--RELATIVE - 10
********
Setting the Scope of a Cursor: DECLARE FilmCursor CURSOR LOCAL/GLOBAL

********
Note:Right Click->Database->Properties->Default Cursor(LOCAL or GLOBAL)
********
Record Set Types for Cursors:
DECLARE FilmCursor CURSOR STATIC/DYNAMIC/CURSOR_FAST_FORWARD

********

Note: CURSOR_FAST_FORWARD( readonly),STATIC( copy of data in tempdb),KetSET(Key values in tempdb),DYNAMIC( change key or nonkey values)
********
Record Locking Options for Cursors:
DECLARE FilmCursor CURSOR READ_ONLY/SCROLL_LOCKS/OPTIMISTIC

Note:OPTIMISTIC(updates when you change ,if inbetween someone changes it keeps its update)

********

********
Combining Cursor Options:
DECLARE FilmCursor CURSOR GLOBAL FORWARD_ONLY STATIC READ_ONLY

********
********
Declaring Cursors for Updating Records:
--Declaring a cursor

DECLARE FilmCursor CURSOR 
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm


FOR UPDATE OF FilmCumulativeOscars

Note:By Default the general declaration of cursor allows update.One can specify Update and specific fields.

********
Running Total:
********
DECLARE @filmOscars  INT
DECLARE @TotalOscars INT

SET @TotalOscars = 0
********
Declaring a Cursor
********

--Declaring a cursor
...Refer Video
FETCH NEXT FROM FilmCursor INTO @FilmOscars
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalOscars += @FilmOscars
PRINT @TotalOscars --comment it out

UPDATE tblFilms  SET FilmCumulativeOscars = @TotalOscars  WHERE CURRENT OF  FilmCursor
FETCH NEXT FROM FilmCursor INTO @FilmOscars
END
.....Refer Video
*********************************************************************************