“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
I hope you are
enjoying with this post! Please share with you friends. Thank you!!