Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
Hi..
I have following 2 tables,
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
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
Edited 23-Dec-12 23:54pm
digimanus26.6K
v3
Comments
Sergey Alexandrovich Kryukov at 24-Dec-12 1:11am
   
Cursor? Why?
—SA
Priyaaammu at 24-Dec-12 1:57am
   
to show rows one by one..
Amir Mahfoozi at 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 at 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 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Ok ... using what you have so far just move the select showing the new details inside the loop
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
  Permalink  
v3
Comments
Priyaaammu at 24-Dec-12 6:09am
   
Thank u so much.......got with your comment
Priyaaammu at 24-Dec-12 7:23am
   
how to get my output in temporary table...
CHill60 at 24-Dec-12 9:03am
   
Create the temporary table up front e.g.
create table #Output (
EmpName varchar(20),
EmpLocation varchar(50),
DeptName varchar(50),
BasicSalary numeric (10,2),
HRA numeric (10,2),
Total numeric (10,2))
then put Insert into #Output select @empname, @emplocation etc etc
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 620
1 Maciej Los 610
2 CPallini 255
3 RyanDev 161
4 Peter Leow 125
0 OriginalGriff 5,784
1 Sergey Alexandrovich Kryukov 4,838
2 Peter Leow 3,009
3 Maciej Los 2,894
4 DamithSL 2,465


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 24 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100