Monday 29 May 2006

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

No comments: