我正在使用TVF根据主要项目经理的部门代码返回记录ID列表. 部门代码可以包括字母和数字字符,因此它被定义为varchar.
这是我当前的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
54390是部门代码,我就是这样称呼它的.
select * from searchDeptCode('54390')
它应该返回2个recordID,但它几乎返回了表中的所有记录. 当我独立运行select声明时,它会返回正确的2条记录.
我确信我错过了一些简单的东西,但又无法准确地说出来.