How do I pass a varchar to a tvf and use that parameter to query for the results SQL Server

Question

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.

Answer 1

You should always (no exceptions) provide a length to your varchar parameters, variables or columns - otherwise, like in the case of a parameter, you might be confronted with the default length of 1 character .....

Try this:

ALTER FUNCTION [dbo].[searchDeptCode](@inDeptCode VARCHAR(50))

and adapt the (50) length to whatever length makes sense in your case.

Also see: Bad habits to kick: declaring VARCHAR without (length) for more background and info