如何在禁用游标时将Int列传入接收Int参数的存储过程

Hey there! Let's work through this problem together. Your current attempt to pass a SELECT query directly as a parameter to MyProcedure won't work because that syntax expects a single scalar value, but SELECT id FROM MyTable likely returns multiple rows. Since you can't use a cursor, here are two reliable alternatives to get this done:

方法1：使用WHILE循环逐行处理 #

This approach uses a temporary table with row numbers to iterate through each id in MyTable and execute the stored procedure for each one. It's great if you need to add extra logic (like logging) between each execution.

-- 先把需要处理的id存入带序号的临时表
SELECT 
    id,
    ROW_NUMBER() OVER (ORDER BY id) AS RowNum
INTO #TempProcessingIds
FROM MyTable;

-- 初始化循环变量
DECLARE @TotalRows INT = (SELECT COUNT(*) FROM #TempProcessingIds);
DECLARE @CurrentRow INT = 1;
DECLARE @CurrentId INT;

-- 循环执行存储过程
WHILE @CurrentRow <= @TotalRows
BEGIN
    -- 获取当前要处理的id
    SELECT @CurrentId = id 
    FROM #TempProcessingIds 
    WHERE RowNum = @CurrentRow;

    -- 调用存储过程
    EXEC MyProcedure @i_input = @CurrentId;

    -- 移动到下一行
    SET @CurrentRow = @CurrentRow + 1;
END

-- 清理临时表
DROP TABLE #TempProcessingIds;

方法2：用动态SQL批量生成执行语句 #

If you prefer a more concise solution (and you're using SQL Server 2017 or later), you can use STRING_AGG to build a single SQL string that calls the stored procedure for every id, then execute it dynamically.

DECLARE @BatchSql NVARCHAR(MAX);

-- 拼接所有EXEC语句
SELECT @BatchSql = STRING_AGG(
    N'EXEC MyProcedure @i_input = ' + CAST(id AS NVARCHAR(10)),
    N';'
)
FROM MyTable;

-- 执行批量语句
EXEC sp_executesql @BatchSql;

为什么你的原代码会报错？ #

Just to clarify: Your original line exec MyProcedure (select id from MyTable) will only work if the SELECT returns exactly one row. If there are multiple rows, you'll get an error saying "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

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