how to calculate total salary of an employee using cursor

Question

2.00/5 (3 votes)

See more:

Hi..
I have following 2 tables,

SQL

create table Employee_Details
(
EmpId	int,
EmpName	varchar(20),
EmpLocation	varchar(50),
BasicSalary	int,
HraDetails	int,
TotalSalary	int
)

create table Department_Details
(
EmpId	int,
DeptId	int,
DeptName	varchar(50)
)

I have to calculate hra with 23% of basic salary and totalsalary using cursor.
Am new to stored procedures,so guide me...

Ok..i have mentioned 2 tables with each having 2 records. I tried with cursor to calculate hra and total salary as below

SQL

declare @empname	varchar(50)
declare @emplocation	 varchar(50)
declare @deptname	varchar(50)
declare	@basicsalary	int
declare	@hra	int
declare	@total	int
declare @hradetails	int
declare @totaldetails	int
declare cur_sample4	cursor
static for
select emp.EmpName,emp.EmpLocation,dep.DeptName,emp.BasicSalary,emp.HraDetails,emp.TotalSalary
from Employee_Details emp inner join Department_Details dep on emp.EmpId=dep.EmpId
open cur_sample4
begin
fetch next from cur_sample4 into @empname,@emplocation,@deptname,@basicsalary,@hradetails,@totaldetails
while @@FETCH_STATUS=0
begin
set @hradetails=@basicsalary*23/100
set @totaldetails=@basicsalary + @hradetails
fetch next from cur_sample4 into @empname,@emplocation,@deptname,@basicsalary,@hradetails,@totaldetails
end
end
close cur_sample4
deallocate cur_sample4
--set nocount off
select @empname as EmpName,@emplocation as EmpLocation,@deptname as Dept
,@basicsalary as BasicSalary,@hradetails as HRA,@totaldetails as Total

But am getting only the second row. Guide me what is the mistake in query

Posted 23-Dec-12 18:56pm

Priyaaammu

Updated 23-Dec-12 23:54pm

Herman<T>.Instance

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 24-Dec-12 1:11am

Cursor? Why?
—SA

Priyaaammu 24-Dec-12 1:57am

to show rows one by one..

Amir Mahfoozi 24-Dec-12 3:12am

It seems that you can update Employee_Details table with a single SQL statement ,so probably no need to use cursors.
Please provide a numerical example.

Priyaaammu 24-Dec-12 5:09am

Ok..i have mentioned 2 tables with each having 2 records. I tried with cursor to calculate hra and total salary as below
declare @empname varchar(50)
declare @emplocation varchar(50)
declare @deptname varchar(50)
declare @basicsalary int
declare @hra int
declare @total int
declare @hradetails int
declare @totaldetails int
declare cur_sample4 cursor
static for
select emp.EmpName,emp.EmpLocation,dep.DeptName,emp.BasicSalary,emp.HraDetails,emp.TotalSalary
from Employee_Details emp inner join Department_Details dep on emp.EmpId=dep.EmpId
open cur_sample4
begin
fetch next from cur_sample4 into @empname,@emplocation,@deptname,@basicsalary,@hradetails,@totaldetails
while @@FETCH_STATUS=0
begin
set @hradetails=@basicsalary*23/100
set @totaldetails=@basicsalary + @hradetails
fetch next from cur_sample4 into @empname,@emplocation,@deptname,@basicsalary,@hradetails,@totaldetails
end
end
close cur_sample4
deallocate cur_sample4
--set nocount off
select @empname as EmpName,@emplocation as EmpLocation,@deptname as Dept
,@basicsalary as BasicSalary,@hradetails as HRA,@totaldetails as Total

But am getting only the second row. Guide me what is the mistake in query

CHill60 24-Dec-12 5:51am

You're looping around the entire table but not updating the table with the calculated values. Your final select is displaying the final contents of the variables. Earlier in the loop they would have contained the data for the first record

2 solutions

Solution 2

while @@FETCH_STATUS=0
begin
set @hradetails=@basicsalary*23/100
set @totaldetails=@basicsalary + @hradetails
fetch next from cur_sample4 into @empname, @emplocation, @deptname, @basicsalary, @hradetails, @totaldetails
end

in following loop you are only calculating the data for current row

select @empname as EmpName,@emplocation as EmpLocation,@deptname as Dept
,@basicsalary as BasicSalary,@hradetails as HRA,@totaldetails as Total
in above line your selecting the last fectched record

use following queries to select calculated data or updating
SELECT emp.EmpName, emp.EmpLocation, dep.DeptName, emp.BasicSalary,
(emp.BasicSalary * 23 / 100) AS HraDetails, (emp.BasicSalary, (emp.BasicSalary * 23 / 100)) AS emp.TotalSalary
FROM Employee_Details emp
INNER JOIN Department_Details dep
ON emp.EmpId = dep.EmpId

For updating Hra and total salary
UPDATE emp SET HraDetails = (BasicSalary * 23 / 100), TotalSalary = (BasicSalary + (BasicSalary * 23 / 100))
FROM Employee_Details emp
INNER JOIN Department_Details dep
ON emp.EmpId = dep.EmpId

Posted 24-Dec-12 0:26am

gagan sawaliya

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2012-12-24T00:01:00

Ok ... using what you have so far just move the select showing the new details inside the loop

SQL

while @@FETCH_STATUS=0
begin
    set @hradetails=@basicsalary*23/100
    set @totaldetails=@basicsalary + @hradetails
     -- display the results as you go along
select @empname as EmpName,@emplocation as EmpLocation,@deptname as Dept      ,@basicsalary as BasicSalary,@hradetails as HRA,@totaldetails as Total 
    fetch next from cur_sample4 into @empname,@emplocation,@deptname,@basicsalary,@hradetails,@totaldetails
end

Note that it is before the fetch next to ensure we don't mess up @@FETCH_STATUS

Also note that if I was doing this I would have BasicSalary, HraDetails and TotalSalary declared as numeric
and would get the results like this ...

update #Employee_Details set HraDetails = BasicSalary * 23.0 / 100.0, TotalSalary = BasicSalary + (BasicSalary * 23.0 / 100.0)
select * from #Employee_Details