SQL SERVER : How to create cursor

What is Cursor?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

• Declare cursor
• Open cursor
• Fetch row from the cursor
• Process fetched row
• Close cursor
• Deallocate cursor

Syntax:

declare @procName varchar(500)
declare cur cursor 

for select [name] from sys.objects where  type = 'P' 
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    print @procName
    exec('drop procedure ' + @procName)
    fetch next from cur into @procName
end
close cur
deallocate cur