No value return in stored procedure

Question

I have this query below

ALTER PROCEDURE sp_myTest
    @emp_ids nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        Count(*) 
    FROM
        emp_service t
    INNER JOIN 
        t_emp e ON t.emp_id = e.emp_id
    WHERE 
        (t.emp_id IN (Select * FROM dbo.Split(@emp_ids, ',')) OR @emp_ids = '' OR @emp_ids IS NULL)
        AND t.status IN ('A', 'R', 'S')
    GROUP BY 
        t.emp_id
END

In the stored procedure above, I have two tables: Employee Service (emp_service) and Employee table t_emp.

What I am trying to do here is to find out the employee service's status. Let's say 'A' is active, 'R' is removed, and 'S' is suspended. In this query, I want to find the number of services that is corresponding to the emp_id.

For example: my emp_id is '15000' and I have three services and their status is 'A', 'S', 'A'. In this case, the expected result should return '3' (I have run it using only the select statement and it return the correct result)

Declare @emp_ids nvarchar(max)

Set @emp_id = '15000'

Select Count(*) 
    From emp_service t
    INNER JOIN t_emp e
    ON t.emp_id = e.emp_id
    WHERE 
        (t.emp_id IN (Select * FROM dbo.Split(@emp_ids, ',')) OR @emp_ids = '' OR @emp_ids IS NULL)
        and t.status in ('A', 'R', 'S')
        GROUP BY t.emp_id

This query return '3' which is the correct result.

However, when I am trying to execute the stored procedure, it return '0'. I am not sure why it come out different result. Help will be appreciated.

No value return in stored procedure

1 Answers