Would give you a fully worked example but I'm just about to go into a meeting.
I did something similar in my article about loops in SQL Server - see
Processing Loops in SQL Server[
^] the section about "Traversing a Hierarchy".
You can then filter (or count!) the results based on the MtoE calculated column
EDIT after OP comment:
Here is a worked example using your column and table names (but note I have used a table variable so remove the "@"). I set up this sample data
declare @tblEmployeeMaster table (emp_no int, emp_name nvarchar(50), mgr_id int)
insert into @tblEmployeeMaster (emp_no, emp_name, mgr_id) values
(1, 'John', NULL),
(2, 'Direct Report 1', 1),(3, 'Direct Report 2', 1),(4, 'Direct Report 3', 1),(5, 'Direct Report 4', 1),(6, 'Direct Report 5', 1),
(7, 'Direct Report 6', 1),(8, 'Direct Report 7', 1),(9, 'Direct Report 8', 1),(10, 'Direct Report 9', 1),(11, 'Direct Report 10', 1),
(12, 'Reports to DR1#1',2),(13, 'Reports to DR1#2',2),
(14, 'Reports to DR2#1',3),
(15, 'Reports to DR3#1',4),(16, 'Reports to DR3#2',4),(17, 'Reports to DR3#3',4)
So John has 16 reportees down the hierarchy.
I set up a variable to capture his emp_no surrounded by "/" but this could be the parameter to a user function. Note the type is varchar not int
DECLARE @IDtoCheck varchar(5) = (SELECT '/' + CAST(emp_no as varchar(5)) + '/' FROM @tblEmployeeMaster WHERE [emp_name] = 'John')
Then I tweaked the CTE from the article as follows
;WITH Emp_CTE AS
(
SELECT emp_no, mgr_id, emp_name
, 1 as RLevel
, MtoE = CAST(isnull(mgr_id,0) AS VARCHAR(MAX)) + '/' +
CAST(emp_no AS VARCHAR(MAX))
FROM @tblEmployeeMaster
WHERE mgr_id IS NULL
UNION ALL
SELECT e.emp_no, e.mgr_id, e.emp_name
, RLevel + 1
, MtoE = MtoE + '/' + CAST(e.emp_no AS VARCHAR(MAX))
FROM @tblEmployeeMaster e
INNER JOIN Emp_CTE ecte ON ecte.emp_no = e.mgr_id
)
SELECT COUNT(*) as TotalReportees
FROM Emp_CTE
where CHARINDEX('/' + @IDtoCheck + '/', MtoE ) > 0