How to create cursors in microsoft sql server
-- how_to_use_cursors 2023,9
alter procedure how_to_use_cursors
@year bigint,
@month bigint
as
begin
declare @employee_id bigint
declare @No_of_days_present bigint
declare @total_no_of_days bigint
declare @Employee_Name varchar(250)
declare @Employee_Designation varchar(250)
declare @Employee_Salary decimal(14,2)
declare @hra decimal(14,2)
declare @da decimal(14,2)
declare @incentive decimal(14,2)
declare @gross_salary decimal(14,2)
declare @pf decimal(14,2)
declare @esic decimal(14,2)
declare @lop decimal(14,2)
declare @deductions decimal(14,2)
declare @net_salary decimal(14,2)
create table #emp
(
employee_id bigint,
employee_name varchar(250),
employee_designation varchar(250),
total_no_of_days bigint,
no_of_days_present bigint,
Loss_of_pay_days bigint,
employee_salary decimal(14,2),
Hra decimal(14,2),
Da decimal(14,2),
incentive decimal(14,2),
Gross_Salary decimal(14,2),
pf decimal(14,2),
esic decimal(14,2),
Lop decimal(14,2),
Deductions decimal(14,2),
Net_Salary decimal(14,2)
)
declare MyCursor cursor static for
select employee_id from employee_details order by Employee_id
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @employee_id
while (@@FETCH_STATUS = 0)
BEGIN
select @No_of_days_present=No_of_days_present ,
@total_no_of_days=Total_Working_Days
from Attendance where Employee_id=@employee_id and [year]=@year and [month]=@month
select
@Employee_Name=Employee_Name,
@Employee_Designation=Employee_Designation,
@Employee_Salary=Employee_Salary
from employee_details where Employee_id=@employee_id
set @hra = @Employee_Salary * 15 * 0.01
set @da = @Employee_Salary * 30 * 0.01
set @incentive = @Employee_Salary * 25 * 0.01
set @gross_salary = @hra + @da + @incentive
set @pf = @gross_salary * 8 * 0.01
set @esic = @gross_salary * 3 * 0.01
set @lop = (@Employee_Salary / @total_no_of_days) * (@total_no_of_days - @No_of_days_present)
set @deductions = @pf + @esic + @lop
set @net_salary = @gross_salary - @deductions
insert into #emp values (@employee_id,@Employee_Name,@Employee_Designation,@total_no_of_days,
@No_of_days_present,
(@total_no_of_days - @No_of_days_present),@Employee_Salary,@hra,@da,@incentive,@gross_salary,
@pf,@esic,@lop,@deductions,@net_salary)
FETCH NEXT FROM MyCursor INTO @employee_id
END
CLOSE MyCursor
DEALLOCATE MyCursor
select * from #emp
end