SQL Server中TVF接收varchar参数后查询结果异常问题排查

问题描述 #

使用表值函数（TVF）根据主项目经理的部门代码返回recordID列表，部门代码包含字母与数字，定义为varchar类型。函数代码如下：

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

调用语句：

select * from searchDeptCode('54390')

预期返回2条recordID，但实际返回表中几乎所有记录；单独执行查询语句时结果正确。

问题原因 #

• SQL Server中，定义varchar类型参数时若不指定长度，默认长度为1。传入'54390'时，参数会被截断为'5'，导致like '%5%'匹配所有包含数字5的部门代码，从而返回大量不符合预期的记录。
• 单独执行查询时直接使用完整的'54390'，不存在参数截断问题，因此结果正确。

解决方案 #

1. 修正参数长度：为@inDeptCode指定合适的varchar长度（根据实际部门代码的最大长度调整，示例中用varchar(20)）：

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

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

GO

2. 移除无效排序：原函数中INSERT语句后的ORDER BY无效，因为表变量的存储顺序不被保证。若需要返回有序结果，应在调用函数时添加排序：

select * from searchDeptCode('54390') ORDER BY recordID

内容的提问来源于stack exchange，提问作者R Loomas