如何使用SQL Cursor（游标）返回满足过滤条件的行数据

核心实现思路：

• 用表变量作为结果容器，暂存所有符合筛选条件的行
• 声明无WHERE子句的游标遍历全表数据
• 循环读取游标每行数据，匹配自定义复杂规则，符合条件则写入结果容器
• 游标遍历完成后直接查询结果容器，统一返回所有符合要求的行

改造后完整存储过程代码 #

CREATE OR ALTER PROCEDURE SpUserSearch
    @Condition BIT = 0, -- AND=0, OR=1.
    @Name NVARCHAR(100) = NULL,
    @Email NVARCHAR(100) = NULL
AS
BEGIN

    SET NOCOUNT ON;

    -- 声明表变量存储最终结果
    DECLARE @ResultUser TABLE (
        Id INT,
        Name NVARCHAR(100),
        Email NVARCHAR(100)
    )

    DECLARE @UseName BIT
    DECLARE @UseEmail BIT
    -- 声明无WHERE子句的游标读取全表数据
    DECLARE UserCursor CURSOR FOR
        SELECT Id, Name, Email FROM [User]
    -- 声明变量存储游标每次读取的单行数据
    DECLARE @Id INT, @CurName NVARCHAR(100), @CurEmail NVARCHAR(100)
    DECLARE @IsMatch BIT

    IF ((@Name IS NULL) OR (LEN(@Name) = 0)) SET @UseName = 0 ELSE SET @UseName = 1
    IF ((@Email IS NULL) OR (LEN(@Email) = 0)) SET @UseEmail = 0 ELSE SET @UseEmail = 1

    -- 打开游标开始循环遍历
    OPEN UserCursor
    FETCH NEXT FROM UserCursor INTO @Id, @CurName, @CurEmail

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @IsMatch = 0

        -- 此处可任意扩展复杂筛选逻辑，无需修改游标定义
        IF @Condition = 0 -- AND逻辑
        BEGIN
            SET @IsMatch = 1
            IF @UseName = 1 AND @CurName NOT LIKE '%' + @Name + '%' SET @IsMatch = 0
            IF @UseEmail = 1 AND @CurEmail NOT LIKE '%' + @Email + '%' SET @IsMatch = 0
        END
        ELSE -- OR逻辑
        BEGIN
            IF (@UseName = 1 AND @CurName LIKE '%' + @Name + '%') OR (@UseEmail = 1 AND @CurEmail LIKE '%' + @Email + '%')
                SET @IsMatch = 1
        END

        -- 符合条件则插入结果表
        IF @IsMatch = 1
        BEGIN
            INSERT INTO @ResultUser (Id, Name, Email) VALUES (@Id, @CurName, @CurEmail)
        END

        -- 读取下一行数据
        FETCH NEXT FROM UserCursor INTO @Id, @CurName, @CurEmail
    END

    -- 释放游标资源
    CLOSE UserCursor
    DEALLOCATE UserCursor

    -- 统一返回所有符合条件的结果
    SELECT * FROM @ResultUser
    RETURN (@@ROWCOUNT)

END

匹配C#伪代码逻辑的循环实现 #

如果需要和你提供的C#伪代码逻辑完全一致（匹配第一个条件后不再判断后续规则），可以把循环内的逻辑替换为下面的代码：

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Added BIT = 0

    IF @Added = 0 AND @UseName = 1 AND @CurName LIKE '%' + @Name + '%'
    BEGIN
        INSERT INTO @ResultUser (Id, Name, Email) VALUES (@Id, @CurName, @CurEmail)
        SET @Added = 1
    END

    IF @Added = 0 AND @UseEmail = 1 AND @CurEmail LIKE '%' + @Email + '%'
    BEGIN
        INSERT INTO @ResultUser (Id, Name, Email) VALUES (@Id, @CurName, @CurEmail)
        SET @Added = 1
    END

    FETCH NEXT FROM UserCursor INTO @Id, @CurName, @CurEmail
END

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