Cursors Example

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor. 
      
CREATE PROCEDURE [dbo].[usp_GetUserLeavesSummaryWithoutCarryOverForReport]          
 (                                                            
 @CompanyId int ,                                                  
 @UserID int        
 )                                                            
AS          
declare @ReportTable table(userid int,LeaveTypeId int,LeaveType varchar(50),LeavesTaken float,UnApproved float,LeavesEntitled float,LeavesPending float,Name varchar(50),DepartmentId int )                  
declare @uservalue int      
declare @uservalues cursor      
      
     
      
begin      
set @uservalues=Cursor for  select TblUsers.UserId  from TblUsers where TblUsers.Company_Id=@CompanyId       
open @uservalues       
fetch next      
from @uservalues into @uservalue      
      
while @@FETCH_STATUS=0      
     
insert into @ReportTable(userid,LeaveTypeId,LeaveType,LeavesTaken,UnApproved,LeavesEntitled,LeavesPending,Name,DepartmentId)                                         
 Select UED.User_ID,UED.LeaveType_ID , LM.LeaveType,                     
     isnull(dbo.[getLeavesCountByUserForReport](LM.LeaveTypeID,@uservalue,@CompanyId,@date),0) as LeavesTaken,                    
                        
     ( select FirstName+' '+SurName from tblUsers where UserID=@uservalue) as Name,      
     ( select Department_Id from tblUsers where UserID=@uservalue) as Departmentid                                                      
      from tblLeaveMaster LM    
      inner join                  
      Tbl_UserLeaveEntitlementDetail UED                     
      on                
     UED.LeaveType_ID=LM.LeaveTypeID                    
     where UED.Company_ID=@CompanyId and UED.User_ID=@uservalue and UED.Entitlement <> 0      
fetch next      
from @uservalues into @uservalue      
end      
close @uservalues      
deallocate @uservalues