Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 procedure needed pls help

Author  Topic 

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2006-11-21 : 05:30:38
Dear Friends,
I have two tables........emp and salgrade

I need a procedure to update emp table with the given % hikes.The grades we should take from salgrade table

if the grade =1, then the hike will be 40%
if the grade =2, then the hike will be 30%
if the grade =3, then the hike will be 20%
if the grade =4, then the hike will be 10%


so whenever i execute this procedure, the table should be automatically updated.



thank you verymuch.


Vinod

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 05:35:29
[code]update e
set e.hike = case
when s.grade = 1 then 0.4
when s.grade = 2 then 0.3
when s.grade = 3 then 0.2
when s.grade = 4 then 0.1
...
from emp e
inner join salgrade s on s.<somecol> = e.<somecol>[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-21 : 05:36:41
[code]Update e
set col = col + case sg.grade
when 1 then col * 0.4
when 2 then col * 0.3
when 3 then col * 0.2
when 4 then col * 0.1
end
from emp e join salgrade sg
on e.id = sg.id
[/code]

Edit: oooooooouch !!!




Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2006-11-21 : 05:39:08
Thank you peso and harsha
thank you very much

Vinod
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 05:41:30
If you want some read-only code,
update       e
set e.hike = (5 - s.grade) / 10
from emp e
inner join salgrade s on s.<somecol> = e.<somecol>
where s.grade between 1 and 4


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-21 : 11:40:30
quote:
Originally posted by Peso

If you want some read-only code,
update       e
set e.hike = (5 - s.grade) / 10
from emp e
inner join salgrade s on s.<somecol> = e.<somecol>
where s.grade between 1 and 4


Peter Larsson
Helsingborg, Sweden


If both are integers wont that give 0 for 3/10, 4/10, etc?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 12:16:48
Yes.
Easily fixed by dividing by 10.0


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-22 : 11:22:21
or by (5 - s.grade)*1.0 / 10


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -