top of page
Post: Blog2_Post
Search

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



14 views0 comments

Recent Posts

See All

Comments


bottom of page