SQL Server Cursor [Why Peoples Hate Cursor?]

“What is a Cursor in SQL Server”? “What is the exact use of Cursor”? “Why do people hate SQL Cursor”?

A Cursor is a database object. Cursor is used, when you need to enumerate table records in row by row basic that means singleton fashion.

Its work likes a RecordSet in the ASP.Net.

We can say that a Cursor is a set of rows with a pointer that identify a current row.

Why is it considered to use cursors in SQL Server?

Why do people hate SQL cursors so much?

Actually, the main reason of avoid cursor is “cursors take up memory and create locks” and also create performance issues.

What are the Alternatives of cursor?

As per my thought, I am trying to use a “while loop” at the place of CURSOR to resolve this locking issues.

The “while loops” are easy to use as a cursor but it’s sometime created more difficulty to understand.

The main advantage of “while loop” is that no objects must be created in memory to facilitate the looping.

How to Create Cursor in Sql Server?

--How to Create Cursor in Sql Server?
--STEP1:  DECLARE A CURSOR
DECLARE @EmpName VARCHAR(50)
DECLARE @EmpDepartment VARCHAR(50)

--STEP2:  DEFINE A CURSOR
DECLARE Cursor_Emply CURSOR FOR
 SELECT EmpName, EmpDepartment FROM Emply

--STEP3: OPEN A CURSOR
OPEN Cursor_Emply

--STEP4: FETCH A CURSOR
FETCH Cursor_Emply INTO @EmpName, @EmpDepartment
WHILE(@@fetch_status=0)
 BEGIN
  PRINT '* EmpName= '+@EmpName
  PRINT '  EmpDepartment= '+@EmpDepartment

  --FETCH CURSOR FOR NEXT ROWS
  FETCH Cursor_Emply INTO @EmpName, @EmpDepartment
 END

--STEP5: CLOSE A CURSOR
CLOSE Cursor_Emply

--STEP6: DEALLOCATE A CURSOR
DEALLOCATE Cursor_Emply

How to Call a Cursor in Store procedure?

--How to Call a Cursor in Store procedure?
--CREATE PROCEDURE
CREATE PROCEDURE SPCursor_Emply
AS BEGIN
 DECLARE @EmpName VARCHAR(50)
 DECLARE @EmpDepartment VARCHAR(50)

DECLARE Cursor_Emply CURSOR FOR
 SELECT EmpName, EmpDepartment FROM Emply

-- OPEN CURSOR
OPEN Cursor_Emply

-- FETCH CURSOR
FETCH Cursor_Emply INTO @EmpName,@EmpDepartment
 WHILE(@@fetch_status=0)
 BEGIN
  PRINT '* EmpName= '+@EmpName
  PRINT '  EmpDepartment= '+@EmpDepartment

  --FETCH CURSOR FOR NEXT ROWS
  FETCH Cursor_Emply INTO @EmpName, @EmpDepartment
 END

-- CLOSE CURSOR
CLOSE Cursor_Emply

-- DEALLOCATE CURSOR
DEALLOCATE Cursor_Emply

END

How to use a “while loop” with select statement in SQL Server?

--WHILE LOOP WITH SELECT STATEMENT
DECLARE @Age INT
SET @Age = 30
 WHILE (@Age >=18)
 BEGIN
  PRINT @Age
  SET @Age = @Age + 1
 END
GO

-- WHILE LOOP WITH CONTINUE AND BREAK KEYWORDS
DECLARE @Age INT
SET @Age = 30
 WHILE (@Age >=18)
 BEGIN
  PRINT @Age
  SET @Age = @Age + 1
  CONTINUE;

  IF @Age = 45 -- WILL NEVER EXECUTED!
  BREAK;
 END
GO