Home > DeveloperSection > Blogs > Cursor in SQL

Cursor in SQL


MSSQL Server SQL 
Ratings:
0 Comment(s)
 621  View(s)
Rate this:

Cursor in SQL

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

 

 


Don't want to miss updates? Please click the below button!

Follow MindStick