Cursors in SQL SERVER

We may need to do some operation each record wise in the record set returned. This is reason for cursors.

The Syntax is as follows

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [;]

The below example shows that we need to print the employee name and employee id in a specific manner as Robert Smith (1000)

DECLARE @EmployeeID int

DECLARE @Name varchar(100)

DECLARE cursor_name CURSOR

FOR

SELECT EmployeeID, FirstName + ' ' + LastName

FROM Employee

OPEN cursor_name

FETCH NEXT FROM cursor_name

INTO @EmployeeID, @Name

WHILE @@FETCH_STATUS = 0

BEGIN

  PRINT @Name + '(' + Convert(varchar(10), @EmployeeID) + ')'

  FETCH NEXT FROM cursor_name

  INTO @EmployeeID, @Name

END

CLOSE cursor_name

      DEALLOCATE cursor_name