I’m using a TVF to return a list of recordID’s based on the deparment code of the primary project manager. The department code can include alpha and numeric characters so it’s defined as a varchar.

This is my current TVF.

ALTER FUNCTION [dbo].[searchDeptCode](@inDeptCode varchar)
RETURNS @recordID TABLE 
(recordID int NOT NULL)
AS
BEGIN
    
    if (@inDeptCode IS NULL)
        INSERT INTO @recordID
            SELECT DISTINCT recordID
            FROM projectRecord ORDER BY recordID

    if (@inDeptCode IS NOT NULL)
        INSERT INTO @recordID
            SELECT DISTINCT recordID
            FROM projectRecord 
            WHERE dbo.getPersonDeptCode(dbo.getPersonID(recordID)) like '%' + @inDeptCode + '%'
            ORDER BY recordID
    RETURN
    
END

GO

This is how I’m calling it where 54390 is the department code.

select * from searchDeptCode('54390')

It should return 2 recordID’s but it’s returning nearly all of the records in the table. When I run the select statement independently, it returns the correct 2 records.

I’m sure I’m missing something simple but can’t put my finger on it.