SQL CURSOR转SELECT语句：如何向关联表值函数传递父表参数

核心使用SQL Server原生支持的CROSS APPLY语法即可实现主表字段参数透传给表值函数的需求，完全替换游标逻辑，性能远高于逐行遍历。

适配原业务逻辑的改写代码 #

完全等效原游标逻辑，直接生成目标@orgusers表数据：

DECLARE @orgusers table (UserID int, PRIMARY KEY (UserID))

INSERT INTO @orgusers(UserID)
SELECT DISTINCT u.UserID
FROM (
    -- 对应游标逻辑中先去重取拼接后的OU路径参数
    SELECT DISTINCT '~' + qlu.MemberValue + '~' AS orgParam
    FROM [gl].VTN_QualificationMemberLU qlu 
    WHERE qlu.MemberType = 45 AND qlu.QualificationID = 1111
) AS orgList
-- 逐行将参数传入表值函数拉取对应ID
CROSS APPLY [gl].GetStudentsByOUPath_BeforeCutoff(@nCompanyid, orgList.orgParam, @dCutOffDate) AS u

测试场景验证代码 #

适配你提供的测试表和测试函数的验证语句：

SELECT DISTINCT CAST(u.value AS INT) AS UserID
FROM (
    SELECT DISTINCT MemberValue AS orgParam
    FROM VTN_QualificationMemberLU
) AS orgList
CROSS APPLY GetStudentsByOUPath_BeforeCutoff(orgList.orgParam) AS u

该实现逻辑和原游标完全一致：先对参数源表做去重，再逐参数调用表值函数，最后对所有返回的ID做全局去重，省略了游标逐行判断、逐次插入判重的 overhead，执行效率会有量级提升。

内容的提问来源于stack exchange，提问作者Samir Hemzeyev