In this blog, I’m explaining about cursor in SQL

A Cursor is a database object to retrieve data from a result set one row at a time.

Life cycle of Cursor

Declare cursor:

A cursor is declared by defining the SQL statement that returns a result set.

Open:

A cursor is opened and populated by executing the SQL statement defined by the cursor.

Fetch:

When  cursor is opened , rows can be fetched from  the cursor one by one or in a block to do  data manipulation.

Close:

After data manipulation , we should close the cursor explicitly.

Deallocate:

Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Example:

Create proc [dbo].[proCursor](@str nvarchar(50))

as

begin

declare @Name varchar(100)

declare @Id varchar(100)

declare cur cursor global static  for select Id,FirstName from EmployeeDetail where firstName=@str

open cur
       fetch next from cur into @Id,@Name
            while @@FETCH_STATUS=0
                   begin                              Print 'Id:'+@Id+' Name:'+@Name
                              fetch next from cur into @Id,@Name
              end     
  close cur
  deallocate cur End

 

 

Leave Comment