locked
How to display salary of employees from the Employee Number RRS feed

  • Question

  • User1242168447 posted

    I'm struggling to make a stored procedure that takes in an employNo and returns the salary amount as well as the department name.

    CREATE PROCEDURE SelectEmployeeNo @empNo varchar(50)
    AS
    select e.employeeNo,
    e.departmentID,
    d.name as departmentName
    from Department as d
    join Employees as e
    on d.departmentID = e.departmentID
    where d.name = @empNo

    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 5:59 PM

Answers

  • User-474980206 posted

    simple:

    CREATE PROCEDURE SelectEmployeeNo @empNo varchar(50)
    AS
    
    set nocount on;
    
    select
      e.employeeNo,
      e.departmentID,
      d.name as departmentName,
      s.Amount
    from Department as d
    join Employees as e
      on d.departmentID = e.departmentID
    join salaryLevel s
      on s.salaryLevelID = e.salaryLevelID
    where d.name = @empNo;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 3, 2020 10:13 PM
  • User1686398519 posted

    Hi,  LetMeCode

    According to your needs, I have modified your code, please refer to it.

    CREATE PROCEDURE SelectEmployeeNo @empNo varchar(50)
    AS
    SELECT 
    d.name as departmentName,
    s.amount
    FROM Department as d,salaryLevel as s,Employees as e
    WHERE E.employeeNo = @empNo and d.departmentID = e.departmentID and e.salaryLevelID=s.salaryLevelID   
    EXEC dbo.SelectEmployeeNo 2;
    • More information on how to use Stored Procedure can refer to the two links:link1,link2.

    Here is the result.

     
    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 4, 2020 7:08 AM

All replies

  • User475983607 posted

    I recommend using the SSMS query designer to generate the query as it seems you are not very strong writing TSQL syntax.  The SSMS query designer is a GUI interface that generates TSQL code.

    https://www.mssqltips.com/sqlservertip/1086/sql-server-management-studio-query-designer/

    Wednesday, June 3, 2020 6:14 PM
  • User-474980206 posted

    simple:

    CREATE PROCEDURE SelectEmployeeNo @empNo varchar(50)
    AS
    
    set nocount on;
    
    select
      e.employeeNo,
      e.departmentID,
      d.name as departmentName,
      s.Amount
    from Department as d
    join Employees as e
      on d.departmentID = e.departmentID
    join salaryLevel s
      on s.salaryLevelID = e.salaryLevelID
    where d.name = @empNo;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 3, 2020 10:13 PM
  • User1686398519 posted

    Hi,  LetMeCode

    According to your needs, I have modified your code, please refer to it.

    CREATE PROCEDURE SelectEmployeeNo @empNo varchar(50)
    AS
    SELECT 
    d.name as departmentName,
    s.amount
    FROM Department as d,salaryLevel as s,Employees as e
    WHERE E.employeeNo = @empNo and d.departmentID = e.departmentID and e.salaryLevelID=s.salaryLevelID   
    EXEC dbo.SelectEmployeeNo 2;
    • More information on how to use Stored Procedure can refer to the two links:link1,link2.

    Here is the result.

     
    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 4, 2020 7:08 AM