Write a stored procedure that takes a department ID as input and returns the department name, employee name, and the second highest salary for that department.

Question

Assisting AI · Accepted Answer

Use a parameterized procedure and a window function to filter by the supplied department ID:

```sql
CREATE PROCEDURE dbo.GetDeptSecondHighestById
    @DeptID INT
AS
BEGIN
    SELECT d.DepartmentName,
           e.EmployeeName,
           e.Salary
    FROM (
        SELECT e.EmployeeName,
               e.Salary,
               DENSE_RANK() OVER (ORDER BY e.Salary DESC) AS SalaryRank
        FROM Employees e
        WHERE e.DepartmentID = @DeptID
    ) AS ranked
    JOIN Departments d ON d.DepartmentID = @DeptID
    WHERE SalaryRank = 2;
END;
```

The procedure returns all employees who earn the second highest salary in the specified department. Complexity remains linear in the number of employees in that department. Call it with `EXEC dbo.GetDeptSecondHighestById @DeptID = 5;`.

Write a stored procedure that takes a department ID as input and returns the department name, employee name, and the second highest salary for that department.

💡 Model Answer

🎤 Get questions like this answered in real-time