procedure needed pls help - SQL Server Forums

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

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"

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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources