locked
How to display update column of each employee in department table RRS feed

  • Question

  • User1242168447 posted

    I'm looking to make a stored procedure that takes in a department name and
    updates the salary of each employee in the department based on the increase percentage.
     
        CREATE PROCEDURE UpdateEmployeeSalaries @name varchar(50)
        AS
        UPDATE e.employeeNo,
                       e.lastName,
                       e.firstName,
                       e.gender,
                       e.IDNumber,
                       e.salaryLevelID,
                       e.departmentID,
                       d.name as departmentName
                from Department as d
                    join Employees as e
                        on d.departmentID = e.departmentID
                where d.name = @name
     
     
    Table structure
     
        [Department]
        (
            [departmentID] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
            [name] VARCHAR(50) NOT NULL,
            [costCentreID] INT FOREIGN KEY REFERENCES CostCentre(costCentreID)
        );
     
     
     
        [Employees]
        (
            [employeeNo] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
            [lastName] VARCHAR(50) NOT NULL,
            [firstName] VARCHAR(50) NOT NULL,
            [gender] CHAR NOT NULL,
            [IDNumber] VARCHAR(20) NOT NULL,
            [salaryLevelID] INT,
            [departmentID] INT FOREIGN KEY REFERENCES Department(departmentID)
        );
     
     
        [salaryLevel]
        (
        [salaryLevelID] INT NOT NULL,
        [amount] DECIMAL(7,2),
        [increasePercentage] SMALLINT
        );

    Wednesday, June 3, 2020 3:29 PM

Answers

  • User475983607 posted

    Can you identify any unintended consequences the above procedure might have?

    The procedure has errors and does not run. 

    IMHO, the design is poor for reason explained above.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 3, 2020 4:04 PM

All replies

  • User475983607 posted

    Can you change the design?  A person's salary is very important.  I would, at least, have a record of the employee's salary with begin and end dates. That way you have a history of what happened.  Let's say you make a mistake in the calculation or miss a group then how do you undo the calculation?

    Wednesday, June 3, 2020 3:45 PM
  • User1242168447 posted

    @ mgebhard

    Can you identify any unintended consequences the above procedure might have?

    Wednesday, June 3, 2020 3:56 PM
  • User475983607 posted

    Can you identify any unintended consequences the above procedure might have?

    The procedure has errors and does not run. 

    IMHO, the design is poor for reason explained above.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 3, 2020 4:04 PM
  • User1242168447 posted

    Thank you so much Sir, for your reply. I'm gonna have a talk with the business analyst who got the requirement and change it if possible cause I cannot change it by myself without explaining them.

    But in the meantime, how do I make the above code work?

    Wednesday, June 3, 2020 4:54 PM
  • User475983607 posted

    LetMeCode

    Thank you so much Sir, for your reply. I'm gonna have a talk with the business analyst who got the requirement and change it if possible cause I cannot change it by myself without explaining them.

    There is nothing wrong with the business requirement.  The design to meet the business requirement has the problem.

    LetMeCode

    But in the meantime, how do I make the above code work?

    I'm not sure.  It is difficult to understand the design intent given the code.  The update statement updates nothing and it throws an error.  it is not clear how the SalaryLevel table is used in the design.  What is the Amount field?  Is Amount a base salary?  What is increasePercentage and why is it a SMALLINT type rather than a DECIMAL?  Does each employ have a SalaryLevelId record?  

    It is unusual for employees to have the same salary which what the current design indicates.  Usually salary to employ is a one-to-one.  If you are trying to apply a percent increase to a group of employees by department (int this case) and apply the update to this group.  I would expect the update process end dates the current salary record and creates a new salary record with an open end date.

    Wednesday, June 3, 2020 5:29 PM
  • User1242168447 posted

    Thanks, I got it.

    Can you please help me with this question here

    Wednesday, June 3, 2020 5:41 PM