Question in Stored procedures

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 4972 Views - Last Post: 08 April 2011 - 02:51 AM Rate Topic: -----

#1 mon mon  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 128
  • Joined: 31-December 09

Question in Stored procedures

Posted 28 March 2011 - 02:19 PM

this question :create tables
Department (Dept_ID, Dept_Name),
Employee (Emp_ID, Emp_name, Salary, Dept_ID) where Dept_ID is a foreign key references Department (Dept_ID)
And you are required to write a stored procedure with name Increase_Salary that takes a department ID and increase the salary of all employees of that department with 0.25 from their stored salary, and then prints the employee name, old salary, and new salary for those employee who their salaries exceeds salary threshold given as input for the stored procedure after their salaries have been updated.
/////////////////////////////////////////////////////////////////
i make code for it but not know it correct thinking or not(if i thought wrong tell me)
create table Department(Dept_ID int primary key , Dept_Name varchar(20))
create table Employee (Emp_ID int ,Emp_name varchar(20),Salary int , Dept_id int references Department(Dept_ID))
create procedure Increase_Salary @DepartmentID int
as
while(select count(Dept_id)from Employee) = @DepartmentID
begin
     update Employee set NewSalary = Salary + 0.25
end
group by Emp_name,Salary,NewSalary
order by Emp_ID
go

This post has been edited by mon mon: 28 March 2011 - 02:21 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Question in Stored procedures

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9487
  • View blog
  • Posts: 35,767
  • Joined: 12-June 08

Re: Question in Stored procedures

Posted 28 March 2011 - 02:22 PM

You don't need to loop through the rows. Just do your 'update' statement where the department id equals the id given.

Think in sets not individual objects of data!
Was This Post Helpful? 0
  • +
  • -

#3 mon mon  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 128
  • Joined: 31-December 09

Re: Question in Stored procedures

Posted 28 March 2011 - 02:35 PM

i try to do it but about where i don't understand how to make update
create table Department(Dept_ID int primary key , Dept_Name varchar(20))
create table Employee (Emp_ID int ,Emp_name varchar(20),Salary int , Dept_id int references Department(Dept_ID))
create procedure Increase_Salary @DepartmentID int
as
select count(Dept_id)from Employee
where set NewSalary = Salary + 0.25
group by Emp_name,Salary,NewSalary
order by Emp_ID
go

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9487
  • View blog
  • Posts: 35,767
  • Joined: 12-June 08

Re: Question in Stored procedures

Posted 28 March 2011 - 02:40 PM

Where did you put the update?

update Employee 
set NewSalary = Salary + 0.25
where <department id column> = id_variable

Was This Post Helpful? 0
  • +
  • -

#5 mon mon  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 128
  • Joined: 31-December 09

Re: Question in Stored procedures

Posted 28 March 2011 - 02:41 PM

can use cursor in this example
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9487
  • View blog
  • Posts: 35,767
  • Joined: 12-June 08

Re: Question in Stored procedures

Posted 28 March 2011 - 02:46 PM

Again - *why*? Why would you want to use a cursor? The update says "everything in this group (selected by your department ID) - your column X will now be the value it was before but increased by 25%"


Oh and if you want a 25% increase you need to multiply "salary*1.25".
Was This Post Helpful? 0
  • +
  • -

#7 mon mon  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 128
  • Joined: 31-December 09

Re: Question in Stored procedures

Posted 28 March 2011 - 02:52 PM

because it want to prints old salary and new salary and Employee name
you read question carefully
[then prints the employee name, old salary, and new salary for those employee who their salaries exceeds salary threshold given as input for the stored procedure after their salaries have been updated. ]
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9487
  • View blog
  • Posts: 35,767
  • Joined: 12-June 08

Re: Question in Stored procedures

Posted 28 March 2011 - 02:55 PM

Wow, really? The snippy tone is not appreciated.

It's an order of operations then, right?

A select statement to show the old and new salaries (based on what ever criteria), followed by an update statement to actually DO the salary update.

Again - it's all about sets.
Was This Post Helpful? 0
  • +
  • -

#9 mon mon  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 128
  • Joined: 31-December 09

Re: Question in Stored procedures

Posted 28 March 2011 - 03:00 PM

this code i reached but not sure about syntax and semantics
create table Department(Dept_ID int primary key , Dept_Name varchar(20))
create table Employee (Emp_ID int ,Emp_name varchar(20),Salary int , Dept_id int references Department(Dept_ID))
create procedure Increase_Salary @DepartmentID int
as
select Emp_name,Dept_id, Salary
from Employee
update Employee 
set NewSalary = Salary + 0.25
where Dept_id = @DepartmentID
group by Emp_name,Salary,NewSalary
order by Emp_ID
go

i hope it correct
Was This Post Helpful? 0
  • +
  • -

#10 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,804
  • Joined: 16-October 07

Re: Question in Stored procedures

Posted 28 March 2011 - 03:18 PM

create procedure Increase_Salary 
	@DepartmentID int
as
	-- why this is here?
	select Emp_name,Dept_id, Salary from Employee

	update Employee 
		-- is there a NewSalary column in Employee?
		-- I don't see one
		set NewSalary = Salary + 0.25
		where Dept_id = @DepartmentID
		-- why?
		-- group by Emp_name,Salary,NewSalary
		-- huh?
		-- order by Emp_ID
go



Perhaps:
create procedure Increase_Salary 
	@DepartmentID int
as
	update Employee 
		set Salary = Salary + 0.25
		where Dept_id = @DepartmentID
go


Was This Post Helpful? 1
  • +
  • -

#11 mon mon  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 128
  • Joined: 31-December 09

Re: Question in Stored procedures

Posted 28 March 2011 - 03:44 PM

what about?
[then prints the employee name, old salary, and new salary for those employee who their salaries exceeds salary threshold given as input for the stored procedure after their salaries have been updated. ]
you can read question to understand me how to print salary and new salary and empName
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9487
  • View blog
  • Posts: 35,767
  • Joined: 12-June 08

Re: Question in Stored procedures

Posted 28 March 2011 - 05:48 PM

Yeah you don't need the group by or order by. Your select isn't showing the 'new salary'. You don't have your extra criteria for "for those employee who their salaries exceeds salary threshold". You also create tables that will be empty anyways so nothing is going to be printed out.
Was This Post Helpful? 0
  • +
  • -

#13 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Question in Stored procedures

Posted 29 March 2011 - 11:17 AM

Are you more frustrated because we won't "send teh codez" or because you feel our help isn't meeting your needs? If it's the former, well tough luck. If it's the latter you need to think of it as a process.

You're defining your tables first which is great, but you need to populate them with some data. It doesn't look like this is part of the assignment. It sounds like the assignment is to give everyone a 25% raise in a specific department then print out their old salaries and their new salaries. One thing it doesn't specify (or I missed it while skimming) was whether you needed to do is print everyone's salary or just those that were in that department.

There are any number of ways you can do this. You can update to the new wage first and then do a display where the old wage is re-calculated, you can display everything where the new wage is calculated and then do the update, or use some other method.

If you're having specific issues with your queries people on here are more than willing to help (as long as you are nice about it), but pretty much no one on here is just going to "send you teh codez!"
Was This Post Helpful? 0
  • +
  • -

#14 mon mon  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 128
  • Joined: 31-December 09

Re: Question in Stored procedures

Posted 01 April 2011 - 02:51 PM

i don't want code i want only to understand and see if my trying correct or there are any thing false and discussion in it make me understand things didn't learn before
Was This Post Helpful? 0
  • +
  • -

#15 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Question in Stored procedures

Posted 02 April 2011 - 01:09 AM

ok, let's take it step by step.
1.

Quote

and increase the salary of all employees of that department with 0.25 from their stored salary,

modi123_1 had good idea when he said that an increase of 25%(or 0.25) means salary*1.25
for this part of the stored procedure what you have to do is an easy update and you have it.
2. the problematic print.

Quote

their salaries exceeds salary threshold given as input for the stored procedure

This is quite clear that you have to add another paramter to your procedure : @SalaryThreshold int
Now to the select part where you have to get all employees with exceeded salary. No newSalary column is accepted to the table because of

Quote

after their salaries have been updated

Basically, you look after those employees with exceeded salaries and compute another column: oldSalary
select 
    Emp_id,
    emp_name,
    (Salary - Salary*0.25) as OldSalary,
    Salary as NewSalary
From employees
where dep_id = @DepartmentID and Salary > @SalaryThreshold



Now, something about your initial code. When working with databases, you have to avoid thinking in a procedural way(classic programming). That while you wrote, it has two possibilities in execution:never enter in the loop or never exist from that loop(and I will let you think by yourself why)

This post has been edited by Ionut: 02 April 2011 - 01:11 AM

Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2