大网数据广告活动
数据库与应用安全

Iterate through a SQL Server result set in T-SQL without a cursor

· 52CE 我爱测

This article describes methods to iterate through a SQL Server result set in Transact-SQL without a cursor.

This article describes methods that you can use to iterate through a result set by using Transact-SQL in SQL Server.

Original product version:   SQL Server
Original KB number:   111401

Summary

This article describes methods that you can use to simulate a cursor-like FETCH-NEXT logic in a stored procedure, trigger, or Transact-SQL batch.

Use Transact-SQL Statements to Iterate Through a Result Set

Here are three methods you can use to iterate through a result set by using Transact-SQL statements. The examples below use the Production.Product table from the AdventureWorks sample database

One method is the use of temp tables. With this method, you create a snapshot of the initial SELECT statement and use it as a basis for cursoring. For example:

/********** example 1 **********/
SET NOCOUNT ON
DROP TABLE IF EXISTS #MYTEMP 
DECLARE @ProductID int

SELECT * INTO #MYTEMP FROM Production.Product

SELECT TOP(1) @ProductID = ProductID FROM #MYTEMP

WHILE @@ROWCOUNT <> 0
BEGIN
    SELECT * FROM #MYTEMP WHERE ProductID = @ProductID
    DELETE FROM #MYTEMP WHERE ProductID = @ProductID
    SELECT TOP(1) @ProductID = ProductID FROM #MYTEMP
END

A second method is to use the min function to walk a table one row at a time. This method catches new rows that were added after the stored procedure begins execution, provided that the new row has a unique identifier greater than the current row that is being processed in the query. For example:

/********** example 2 **********/
SET NOCOUNT ON
DROP TABLE IF EXISTS #MYTEMP 
DECLARE @ProductID int

SELECT @ProductID = min( ProductID ) FROM Production.Product
WHILE @ProductID IS NOT NULL

BEGIN
    SELECT * FROM Production.Product WHERE ProductID = @ProductID
    SELECT @ProductID = min( ProductID ) FROM Production.Product WHERE ProductID > @ProductID
END

Note: Both example 1 and 2 assume that a unique identifier exists for each row in the source table. In some cases, no unique identifier may exist. If that's the case, you can modify the temp table method to use a newly created key column. For example:

/********** example 3 **********/
SET NOCOUNT ON
DROP TABLE IF EXISTS #MYTEMP 

SELECT NULL AS mykey, * INTO #MYTEMP FROM Production.Product

UPDATE TOP(1) #MYTEMP SET mykey = 1

WHILE @@ROWCOUNT > 0
BEGIN
    SELECT * FROM #MYTEMP WHERE mykey = 1
    DELETE FROM #MYTEMP WHERE mykey = 1
    UPDATE TOP(1) #MYTEMP SET mykey = 1
END

References

ROW_NUMBER (Transact-SQL)

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 03/11/2025
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。