在 SQL Server 中的存储过程中循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22435195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loop in stored procedure in SQL server
提问by user2831001
I need help with writing stored procedure that calls another stored procedure and passes values to it. So far this was done in C#, now I want to move it to stored procedure and make an SQL agent job that calls it at specific time. Any ideas? This is the case.
我需要帮助编写调用另一个存储过程并将值传递给它的存储过程。到目前为止，这是在 C# 中完成的，现在我想将它移动到存储过程并创建一个在特定时间调用它的 SQL 代理作业。有任何想法吗？情况就是这样。
Table A:
Table A：
PK_TableA_ID
Table B:
Table B：
PK_TableB_ID
Stored procedure SP1:
存储过程SP1：
@TableA_ID
@TableB_ID
I need this but in T-SQL
我需要这个，但在 T-SQL 中
foreach(var TableAId in TableA)
{
foreach(var TableBId in TableB)
{
//call stored procedure
SP1(TableAId, TableBId);
}
}
回答by jpw
Here's an example of how you can use cursors to do loops:
以下是如何使用游标执行循环的示例：
-- set up some test data
declare @table_a table (PK_TableA_ID int)
declare @table_b table (PK_TableB_ID int)
insert @table_a values (1),(2),(3)
insert @table_b values (4),(5),(6)
-- do the actual processing
SET NOCOUNT ON
DECLARE @TableA_ID int, @TableB_ID int
DECLARE TableA_cursor CURSOR FOR SELECT PK_TableA_ID FROM @table_a
OPEN TableA_cursor
FETCH NEXT FROM TableA_cursor INTO @TableA_ID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE TableB_cursor CURSOR FOR SELECT PK_TableB_ID FROM @table_b
OPEN TableB_cursor
FETCH NEXT FROM TableB_cursor INTO @TableB_ID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CAST(@TableA_ID AS CHAR(1)) + ':' + CAST(@TableB_ID AS CHAR(1))
-- execute your stored procedure here:
-- EXEC Your_stored_procedure (@TableA_ID, @TableB_ID)
FETCH NEXT FROM TableB_cursor INTO @TableB_ID
END
CLOSE TableB_cursor
DEALLOCATE TableB_cursor
FETCH NEXT FROM TableA_cursor INTO @TableA_ID
END
CLOSE TableA_cursor
DEALLOCATE TableA_cursor
The cursor above (with the test data in the temporary tables) will generate this output:
上面的光标（带有临时表中的测试数据）将生成以下输出：
1:4
1:5
1:6
2:4
2:5
2:6
3:4
3:5
3:6
Using cursors might not be the best way to solve your problem though.
但是，使用游标可能不是解决问题的最佳方法。
