10. Stored procedures

CREATE OR ALTER PROCEDURE dbo.usp_GetEmployeesByDept
    @DeptId INT,
    @MinSalary DECIMAL(12,2) = 0,          -- default
    @TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;                        -- suppress "n rows affected" messages
    SELECT EmpId, FullName, Salary FROM dbo.Employees
    WHERE DeptId = @DeptId AND Salary >= @MinSalary
    ORDER BY Salary DESC;

    SELECT @TotalCount = @@ROWCOUNT;
    RETURN 0;                              -- return value: status code (INT only)
END;
GO

DECLARE @n INT;
EXEC dbo.usp_GetEmployeesByDept @DeptId = 1, @MinSalary = 50000, @TotalCount = @n OUTPUT;
SELECT @n;