Stored Procedures - Output Parameters

Demonstrates the use of the OUTPUT parameter from a stored procedure -

CREATE PROCEDURE dbo.GetCountByLastName (
@DepartmentName NVARCHAR(50),
@DepartmentCount INT OUTPUT )
AS
select @DepartmentCount = COUNT(*) from HumanResources.EmployeeDepartmentHistory h
inner join HumanResources.Department d
on h.DepartmentID = d.DepartmentID
where d.Name = @DepartmentName and EndDate IS NULL
group by d.Name

Call the procedure like this to fetch the output -

declare @intDepartmentCount int

exec dbo.GetCountByLastName
@DepartmentName = 'Marketing',
@DepartmentCount = @intDepartmentCount OUTPUT

select @intDepartmentCount  as DepartmentCount