SQL Server循环—如何循环遍历一组记录

how do I loop through a set of records from a select?

如何循环遍历select中的一组记录?

So say for example I have a few records that I wish to loop through and do something with each record. Here's a primitive version of my select:

举个例子，我有一些记录我想要循环并对每个记录做一些事情。以下是我选择的原始版本:

select top 1000 * from dbo.table
where StatusID = 7

Thanks

谢谢

7 个解决方案

#1

133

By using T-SQL and cursors like this :

使用T-SQL和游标如下:

DECLARE @MyCursor CURSOR;
DECLARE @MyField YourFieldDataType;
BEGIN
    SET @MyCursor = CURSOR FOR
    select top 1000 YourField from dbo.table
        where StatusID = 7      

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
      /*
         YOUR ALGORITHM GOES HERE   
      */
      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

#2

This is what I've been doing if you need to do something iterative... but it would be wise to look for set operations first.

这是我一直在做的如果你需要做一些迭代的事情……但明智的做法是先寻找固定操作。

select top 1000 TableID
into #ControlTable 
from dbo.table
where StatusID = 7

declare @TableID int

while exists (select * from #ControlTable)
begin

    select top 1 @TableID = TableID
    from #ControlTable
    order by TableID asc

    -- Do something with your TableID

    delete #ControlTable
    where TableID = @TableID

end

drop table #ControlTable

#3

Small change to sam yi's answer (for better readability):

对sam yi的答案做一个小改动(为了更好的可读性):

select top 1000 TableID
into #ControlTable 
from dbo.table
where StatusID = 7

declare @TableID int

while exists (select * from #ControlTable)
begin

    select @TableID = (select top 1 TableID
                       from #ControlTable
                       order by TableID asc)

    -- Do something with your TableID

    delete #ControlTable
    where TableID = @TableID

end

drop table #ControlTable

#4

Just another approach if you are fine using temp tables.I have personally tested this and it will not cause any exception (even if temp table does not have any data.)

如果您喜欢使用临时表，也可以使用另一种方法。我亲自测试过，它不会导致任何异常(即使临时表没有任何数据)。

CREATE TABLE #TempTable
(
    ROWID int identity(1,1) primary key,
    HIERARCHY_ID_TO_UPDATE int,
)

--create some testing data
--INSERT INTO #TempTable VALUES(1)
--INSERT INTO #TempTable VALUES(2)
--INSERT INTO #TempTable VALUES(4)
--INSERT INTO #TempTable VALUES(6)
--INSERT INTO #TempTable VALUES(8)

DECLARE @MAXID INT, @Counter INT

SET @COUNTER = 1
SELECT @MAXID = COUNT(*) FROM #TempTable

WHILE (@COUNTER <= @MAXID)
BEGIN
    --DO THE PROCESSING HERE 
    SELECT @HIERARCHY_ID_TO_UPDATE = PT.HIERARCHY_ID_TO_UPDATE
    FROM #TempTable AS PT
    WHERE ROWID = @COUNTER

    SET @COUNTER = @COUNTER + 1
END


IF (OBJECT_ID('tempdb..#TempTable') IS NOT NULL)
BEGIN
    DROP TABLE #TempTable
END

#5

You could choose to rank your data and add a ROW_NUMBER and count down to zero while iterate your dataset.

您可以选择对数据进行排序，并在迭代数据集时添加ROW_NUMBER并将计数减为0。

-- Get your dataset and rank your dataset by adding a new row_number
SELECT  TOP 1000 A.*, ROW_NUMBER() OVER(ORDER BY A.ID DESC) AS ROW
INTO #TEMPTABLE 
FROM DBO.TABLE AS A
WHERE STATUSID = 7;

--Find the highest number to start with
DECLARE @COUNTER INT = (SELECT MAX(ROW) FROM #TEMPTABLE);
DECLARE @ROW INT;

-- Loop true your data until you hit 0
WHILE (@COUNTER != 0)
BEGIN

    SELECT @ROW = ROW
    FROM #TEMPTABLE
    WHERE ROW = @COUNTER
    ORDER BY ROW DESC

    --DO SOMTHING COOL  

    -- SET your counter to -1
    SET @COUNTER = @ROW -1
END

DROP TABLE #TEMPTABLE

#6

this way we can iterate into table data.

这样我们就可以迭代到表数据。

DECLARE @_MinJobID INT
DECLARE @_MaxJobID INT
CREATE  TABLE #Temp (JobID INT)

INSERT INTO #Temp SELECT * FROM DBO.STRINGTOTABLE(@JobID,',')
SELECT @_MinJID = MIN(JobID),@_MaxJID = MAX(JobID)  FROM #Temp

    WHILE @_MinJID <= @_MaxJID
    BEGIN

        INSERT INTO Mytable        
        (        
            JobID,        
        )        

        VALUES        
        (        
            @_MinJobID,        
        ) 

        SET @_MinJID = @_MinJID + 1;
    END

DROP TABLE #Temp

STRINGTOTABLE is user define function which will parse comma separated data and return table. thanks

STRINGTOTABLE是用户定义函数，用于解析逗号分隔的数据和返回表。谢谢

#7

By using cursor you can easily iterate through records individually and print records separately or as a single message including all the records.

通过使用游标，您可以轻松地逐个遍历记录，并分别打印记录或作为包含所有记录的单个消息。

DECLARE @CustomerID as INT;
declare @msg varchar(max)
DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT CustomerID FROM Customer WHERE CustomerID IN ('3908745','3911122','3911128','3911421')

OPEN @BusinessCursor;
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @msg = '{
              "CustomerID": "'+CONVERT(varchar(10), @CustomerID)+'",
              "Customer": {
                "LastName": "LastName-'+CONVERT(varchar(10), @CustomerID) +'",
                "FirstName": "FirstName-'+CONVERT(varchar(10), @CustomerID)+'",    
              }
            }|'
        print @msg
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
END

#1

133