stored procedure return value
CREATE PROCEDURE GetDepartmentRecord
AS
DECLARE @DepartmentRow AS INT
SELECT @DepartmentRow=COUNT(*) FROM SampleDepartments
RETURN @DepartmentRow
GO
DECLARE @return_value int
EXEC @return_value = GetDepartmentRecord
SELECT 'Return Value' = @return_value
GO