Cursor in MS-SQL Server Stored Procedures

Bangalore: As per your requirement for looping purpose we use cursor in our stored procedures, stored procedures is nothing but a set of queries, If you wont have any other options then only you have to use cursor in your queries because it will affect the normalization of your sql query.

So consider you have 100 employees and you need to generate the payroll  for these 100 employees, so you have write some complex payroll logic and there you want to pass employee id's and it will generate payroll and insert it into a  table.
This kind of situations we need to do operations row by row or employee by employee one employees payroll has generated then next's like that so we will use cursor in that case.


SQL Syntax


/*----Our Code Starts Here ---*/

USE [Tonz] --database name
GO

-- =============================================
-- Author: Tony Tom
-- Create date: 03-06-2013
-- Description: SQL Cursor Example
-- =============================================
ALTER  PROCEDURE [dbo].[USP_CursorTest]  --Stored procedure name

AS
BEGIN
declare @userid varchar(50); --declare one variable to fetch userid's

   declare db_cursor Cursor For -- declare cursor
     select UserID from UserMain 
         open db_cursor  --opening cursor

              Fetch Next from  db_cursor into @userid -- fetching first value

                    while @@FETCH_STATUS=0 -- checking loop exiting condition
                     begin
                          /* -- do your raw by row operation here--*/
                          insert into uname(usename) values(@userid);
                     fetch next from db_cursor into @userid   --fetching next value

                      end
close db_cursor;
End
/*----Our Code Ends Here ---*/

Here we need only one variable in cursor as per your requirements you can take as many variables you want in that particular row to cursor