SQL Server存储过程中，如何用TVP两列在CASE与WHERE子句中迭代操作？

你的需求完全可以实现，但原代码存在两个核心问题：

• CASE是表达式，只能返回单个值，不能用来执行UPDATE这类数据操作语句
• TVP是表类型变量，不能直接通过@parIndexTable.referenceType引用列，需要通过集合关联或逐行遍历的方式访问每行数据

下面给出两种可行的实现方案：

推荐方案：集合式处理（高效符合SQL设计） #

直接通过JOIN关联TVP和目标表，按referenceType分支处理，这是SQL最擅长的集合操作模式：

CREATE PROCEDURE usp.Test
    @parIndexTable  tt_Index READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- 处理'ref1'类型：查询匹配referenceID的记录
    SELECT nc.*
    FROM NamesCurrent nc
    INNER JOIN @parIndexTable tvp 
        ON nc.referenceID = tvp.referenceID
    WHERE tvp.referenceType = 'ref1';

    -- 处理'ref2'类型：更新匹配referenceID的记录
    UPDATE nc
    SET nc.Name = 'Craig'
    FROM NamesCurrent nc
    INNER JOIN @parIndexTable tvp 
        ON nc.referenceID = tvp.referenceID
    WHERE tvp.referenceType = 'ref2';
END

方案优势 #

• 集合操作是SQL的原生优化方向，性能远高于逐行遍历
• 代码简洁，逻辑清晰，便于维护和后续扩展

备选方案：游标逐行处理（适用于复杂逐行逻辑） #

如果业务逻辑必须逐行处理TVP中的记录（比如存在更复杂的分支判断），可以用游标遍历：

CREATE PROCEDURE usp.Test
    @parIndexTable  tt_Index READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明变量存储每行的类型和ID
    DECLARE @refType varchar(20), @refID varchar(20);

    -- 声明游标遍历TVP的所有行
    DECLARE tvpCursor CURSOR FOR
        SELECT referenceType, referenceID
        FROM @parIndexTable;

    OPEN tvpCursor;
    FETCH NEXT FROM tvpCursor INTO @refType, @refID;

    -- 循环处理每一行
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @refType = 'ref1'
        BEGIN
            -- 根据referenceID查询记录
            SELECT * FROM NamesCurrent WHERE referenceID = @refID;
        END
        ELSE IF @refType = 'ref2'
        BEGIN
            -- 根据referenceID更新记录
            UPDATE NamesCurrent 
            SET Name = 'Craig' 
            WHERE referenceID = @refID;
        END

        FETCH NEXT FROM tvpCursor INTO @refType, @refID;
    END

    -- 清理游标
    CLOSE tvpCursor;
    DEALLOCATE tvpCursor;
END

注意事项 #

• 游标会逐行处理数据，性能远低于集合操作，仅在无法用集合实现复杂逻辑时使用
• 务必在使用后关闭并释放游标，避免资源泄漏

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