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