是否有一种方法可以在不使用游标的情况下对TSQL中的表变量进行循环?

Let's say I have the following simple table variable:

假设我有一个简单的表变量:

declare @databases table
(
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)
-- insert a bunch rows into @databases

Is declaring and using a cursor my only option if I wanted to iterate through the rows? Is there another way?

如果我想要遍历行，声明和使用游标是我唯一的选择吗?有另一种方式吗?

21 个解决方案

#1

First of all you should be absolutely sure you need to iterate through each row - set based operations will perform faster in every case I can think of and will normally use simpler code.

首先，您应该绝对确定您需要遍历每一行——基于行集的操作在我能想到的每种情况下都会执行得更快，并且通常会使用更简单的代码。

Depending on your data it may be possible to loop just using select statements as shown below:

根据您的数据，您可以使用select语句进行循环，如下所示:

Declare @Id int

While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
    Select Top 1 @Id = Id From ATable Where Processed = 0

    --Do some processing here

    Update ATable Set Processed = 1 Where Id = @Id 

End

Another alternative is to use a temporary table:

另一种选择是使用临时表:

Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

The option you should choose really depends on the structure and volume of your data.

您应该选择的选项实际上取决于您的数据的结构和容量。

Note: If you are using SQL Server you would be better served using:

注意:如果您使用的是SQL Server，最好使用:

WHILE EXISTS(SELECT * FROM #Temp)

Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one (see Josef's answer below).

使用COUNT将必须触摸表中的每一行，现有的只需要触摸第一行(请参阅下面的Josef的答案)。

#2

Just a quick note, if you are using SQL Server, the examples that have:

简单说明一下，如果您正在使用SQL Server，示例如下:

While (Select Count(*) From #Temp) > 0

Would be better served with

更好的服务是什么

While EXISTS(SELECT * From #Temp)

The Count will have to touch every single row in the table, the EXISTS only needs to touch the first one.

计数将必须接触表中的每一行，存在只需要触摸第一个。

#3

This is how I do it:

我是这样做的:

declare @RowNum int, @CustId nchar(5), @Name1 nchar(25)

select @CustId=MAX(USERID) FROM UserIDs     --start with the highest ID
Select @RowNum = Count(*) From UserIDs      --get total number of records
WHILE @RowNum > 0                          --loop until no more records
BEGIN   
    select @Name1 = username1 from UserIDs where USERID= @CustID    --get other info from that row
    print cast(@RowNum as char(12)) + ' ' + @CustId + ' ' + @Name1  --do whatever

    select top 1 @CustId=USERID from UserIDs where USERID < @CustID order by USERID desc--get the next one
    set @RowNum = @RowNum - 1                               --decrease count
END

No Cursors, no temporary tables, no extra columns. The USERID column must be a unique integer, as most Primary Keys are.

没有游标，没有临时表，没有额外的列。USERID列必须是唯一的整数，因为大多数主键都是。

#4

Define your temp table like this -

像这样定义您的临时表

declare @databases table
(
    RowID int not null identity(1,1) primary key,
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)

-- insert a bunch rows into @databases

Then do this -

然后做这个,

declare @i int
select @i = min(RowID) from @databases
declare @max int
select @max = max(RowID) from @databases

while @i <= @max begin
    select DatabaseID, Name, Server from @database where RowID = @i --do some stuff
    set @i = @i + 1
end

#5

Here is how I would do it:

我是这样做的:

Select Identity(int, 1,1) AS PK, DatabaseID
Into   #T
From   @databases

Declare @maxPK int;Select @maxPK = MAX(PK) From #T
Declare @pk int;Set @pk = 1

While @pk <= @maxPK
Begin

    -- Get one record
    Select DatabaseID, Name, Server
    From @databases
    Where DatabaseID = (Select DatabaseID From #T Where PK = @pk)

    --Do some processing here
    -- 

    Select @pk = @pk + 1
End

---

[Edit] Because I probably skipped the word "variable" when I first time read the question, here is an updated response...

[编辑]因为我可能在第一次读问题的时候跳过了“变量”这个词，所以这里有一个更新的回复……

---

declare @databases table
(
    PK            int IDENTITY(1,1), 
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)
-- insert a bunch rows into @databases
--/*
INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,'MainDB', 'MyServer'
INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,'MyDB',   'MyServer2'
--*/

Declare @maxPK int;Select @maxPK = MAX(PK) From @databases
Declare @pk int;Set @pk = 1

While @pk <= @maxPK
Begin

    /* Get one record (you can read the values into some variables) */
    Select DatabaseID, Name, Server
    From @databases
    Where PK = @pk

    /* Do some processing here */
    /* ... */ 

    Select @pk = @pk + 1
End

#6

If you have no choice than to go row by row creating a FAST_FORWARD cursor. It will be as fast as building up a while loop and much easier to maintain over the long haul.

如果您没有选择，则要按行创建FAST_FORWARD游标。它将与建立一个while循环一样快，并且更容易长期维护。

FAST_FORWARD Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

FAST_FORWARD指定了一个只提供了性能优化的转发型游标。如果还指定了滚动或FOR_UPDATE，则无法指定FAST_FORWARD。

#7

Another approach without having to change your schema or using temp tables:

另一种无需更改模式或使用临时表的方法:

DECLARE @rowCount int = 0
  ,@currentRow int = 1
  ,@databaseID int
  ,@name varchar(15)
  ,@server varchar(15);

SELECT @rowCount = COUNT(*)
FROM @databases;

WHILE (@currentRow <= @rowCount)
BEGIN
  SELECT TOP 1
     @databaseID = rt.[DatabaseID]
    ,@name = rt.[Name]
    ,@server = rt.[Server]
  FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY t.[DatabaseID], t.[Name], t.[Server]
       ) AS [RowNumber]
      ,t.[DatabaseID]
      ,t.[Name]
      ,t.[Server]
    FROM @databases t
  ) rt
  WHERE rt.[RowNumber] = @currentRow;

  EXEC [your_stored_procedure] @databaseID, @name, @server;

  SET @currentRow = @currentRow + 1;
END

#8

You can use a while loop:

你可以使用while循环:

While (Select Count(*) From #TempTable) > 0
Begin
    Insert Into @Databases...

    Delete From #TempTable Where x = x
End

#9

I really do not see the point why you would need to resort to using dreaded cursor. But here is another option if you are using SQL Server version 2005/2008
Use Recursion

我真的不明白为什么需要使用可怕的游标。但是如果您正在使用SQL Server版本2005/2008使用递归，这里还有一个选项

declare @databases table
(
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)

--; Insert records into @databases...

--; Recurse through @databases
;with DBs as (
    select * from @databases where DatabaseID = 1
    union all
    select A.* from @databases A 
        inner join DBs B on A.DatabaseID = B.DatabaseID + 1
)
select * from DBs

#10

I'm going to provide the set-based solution.

我将提供基于集合的解决方案。

insert  @databases (DatabaseID, Name, Server)
select DatabaseID, Name, Server 
From ... (Use whatever query you would have used in the loop or cursor)

This is far faster than any looping techique and is easier to write and maintain.

这比任何循环技术要快得多，而且更容易编写和维护。

#11

-- [PO_RollBackOnReject]  'FININV10532'
alter procedure PO_RollBackOnReject
@CaseID nvarchar(100)

AS
Begin
SELECT  *
INTO    #tmpTable
FROM   PO_InvoiceItems where CaseID = @CaseID

Declare @Id int
Declare @PO_No int
Declare @Current_Balance Money


While (Select ROW_NUMBER() OVER(ORDER BY PO_LineNo DESC) From #tmpTable) > 0
Begin
        Select Top 1 @Id = PO_LineNo, @Current_Balance = Current_Balance,
        @PO_No = PO_No
        From #Temp
        update PO_Details
        Set  Current_Balance = Current_Balance + @Current_Balance,
            Previous_App_Amount= Previous_App_Amount + @Current_Balance,
            Is_Processed = 0
        Where PO_LineNumber = @Id
        AND PO_No = @PO_No
        update PO_InvoiceItems
        Set IsVisible = 0,
        Is_Processed= 0
        ,Is_InProgress = 0 , 
        Is_Active = 0
        Where PO_LineNo = @Id
        AND PO_No = @PO_No
End
End

#12

Lightweight, without having to make extra tables, if you have an integer ID on the table

轻量级的，不需要创建额外的表，如果表上有一个整数ID

Declare @id int = 0, @anything nvarchar(max)
WHILE(1=1) BEGIN
  Select Top 1 @anything=[Anything],@id=@id+1 FROM Table WHERE ID>@id
  if(@@ROWCOUNT=0) break;

  --Process @anything

END

#13

I agree with the previous post that set-based operations will typically perform better, but if you do need to iterate over the rows here's the approach I would take:

我同意前一篇文章的观点，即基于集合的操作通常会表现得更好，但如果您确实需要对行进行迭代，我将采用以下方法:

1. Add a new field to your table variable (Data Type Bit, default 0)
2. 将新字段添加到表变量(数据类型Bit，默认为0)
3. Insert your data
4. 插入你的数据
5. Select the Top 1 Row where fUsed = 0 (Note: fUsed is the name of the field in step 1)
6. 选择熔接= 0的前一行(注意:熔接是步骤1中的字段名)
7. Perform whatever processing you need to do
8. 执行您需要执行的任何处理
9. Update the record in your table variable by setting fUsed = 1 for the record
10. 通过为记录设置fuse = 1来更新表变量中的记录

11. Select the next unused record from the table and repeat the process

    从表中选择下一个未使用的记录并重复此过程

    DECLARE @databases TABLE  
    (  
        DatabaseID  int,  
        Name        varchar(15),     
        Server      varchar(15),   
        fUsed       BIT DEFAULT 0  
    ) 
    
    -- insert a bunch rows into @databases
    
    DECLARE @DBID INT
    
    SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0 
    
    WHILE @@ROWCOUNT <> 0 and @DBID IS NOT NULL  
    BEGIN  
        -- Perform your processing here  
    
        --Update the record to "used" 
    
        UPDATE @databases SET fUsed = 1 WHERE DatabaseID = @DBID  
    
        --Get the next record  
        SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0   
    END
    

#14

This will work in SQL SERVER 2012 version.

这将在SQL SERVER 2012版本中工作。

declare @Rowcount int 
select @Rowcount=count(*) from AddressTable;

while( @Rowcount>0)
  begin 
 select @Rowcount=@Rowcount-1;
 SELECT * FROM AddressTable order by AddressId desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY;
end 

#15

I prefer using the Offset Fetch if you have a unique ID you can sort your table by:

我更喜欢使用偏移量取回，如果你有一个唯一的ID，你可以通过:

DECLARE @TableVariable (ID int, Name varchar(50));
DECLARE @RecordCount int;
SELECT @RecordCount = COUNT(*) FROM @TableVariable;

WHILE @RecordCount > 0
BEGIN
SELECT ID, Name FROM @TableVariable ORDER BY ID OFFSET @RecordCount - 1 FETCH NEXT 1 ROW;
SET @RecordCount = @RecordCount - 1;
END

This way I don't need to add fields to the table or use a window function.

这样，我就不需要向表中添加字段或使用窗口函数。

#16

It's possible to use a cursor to do this:

可以使用光标来实现这一点:

create function [dbo].f_teste_loop returns @tabela table ( cod int, nome varchar(10) ) as begin

创建函数[dbo]。f_teste_loop返回@tabela表(cod int, nome varchar(10))作为begin

insert into @tabela values (1, 'verde');
insert into @tabela values (2, 'amarelo');
insert into @tabela values (3, 'azul');
insert into @tabela values (4, 'branco');

return;

end

结束

create procedure [dbo].[sp_teste_loop] as begin

创建过程[dbo]。(sp_teste_loop)作为开始

DECLARE @cod int, @nome varchar(10);

DECLARE curLoop CURSOR STATIC LOCAL 
FOR
SELECT  
    cod
   ,nome
FROM 
    dbo.f_teste_loop();

OPEN curLoop;

FETCH NEXT FROM curLoop
           INTO @cod, @nome;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT @nome;

    FETCH NEXT FROM curLoop
           INTO @cod, @nome;
END

CLOSE curLoop;
DEALLOCATE curLoop;

end

结束

#17

Here's my solution, which makes use of an infinite loop, the BREAK statement, and the @@ROWCOUNT function. No cursors or temporary table are necessary, and I only need to write one query to get the next row in the @databases table:

下面是我的解决方案，它使用了无限循环、BREAK语句和@@ROWCOUNT函数。不需要游标或临时表，我只需要编写一个查询就可以获得@databases表中的下一行:

declare @databases table
(
    DatabaseID    int,
    [Name]        varchar(15),   
    [Server]      varchar(15)
);


-- Populate the [@databases] table with test data.
insert into @databases (DatabaseID, [Name], [Server])
select X.DatabaseID, X.[Name], X.[Server]
from (values 
    (1, 'Roger', 'ServerA'),
    (5, 'Suzy', 'ServerB'),
    (8675309, 'Jenny', 'TommyTutone')
) X (DatabaseID, [Name], [Server])


-- Create an infinite loop & ensure that a break condition is reached in the loop code.
declare @databaseId int;

while (1=1)
begin
    -- Get the next database ID.
    select top(1) @databaseId = DatabaseId 
    from @databases 
    where DatabaseId > isnull(@databaseId, 0);

    -- If no rows were found by the preceding SQL query, you're done; exit the WHILE loop.
    if (@@ROWCOUNT = 0) break;

    -- Otherwise, do whatever you need to do with the current [@databases] table row here.
    print 'Processing @databaseId #' + cast(@databaseId as varchar(50));
end

#18

This is the code that I am using 2008 R2. This code that I am using is to build indexes on key fields (SSNO & EMPR_NO) n all tales

这是我使用2008 R2的代码。我正在使用的这段代码是在关键字段(SSNO和EMPR_NO)上构建索引

if object_ID('tempdb..#a')is not NULL drop table #a

select 'IF EXISTS (SELECT name FROM sysindexes WHERE name ='+CHAR(39)+''+'IDX_'+COLUMN_NAME+'_'+SUBSTRING(table_name,5,len(table_name)-3)+char(39)+')' 
+' begin DROP INDEX [IDX_'+COLUMN_NAME+'_'+SUBSTRING(table_name,5,len(table_name)-3)+'] ON '+table_schema+'.'+table_name+' END Create index IDX_'+COLUMN_NAME+'_'+SUBSTRING(table_name,5,len(table_name)-3)+ ' on '+ table_schema+'.'+table_name+' ('+COLUMN_NAME+') '   'Field'
,ROW_NUMBER() over (order by table_NAMe) as  'ROWNMBR'
into #a
from INFORMATION_SCHEMA.COLUMNS
where (COLUMN_NAME like '%_SSNO_%' or COLUMN_NAME like'%_EMPR_NO_')
    and TABLE_SCHEMA='dbo'

declare @loopcntr int
declare @ROW int
declare @String nvarchar(1000)
set @loopcntr=(select count(*)  from #a)
set @ROW=1  

while (@ROW <= @loopcntr)
    begin
        select top 1 @String=a.Field 
        from #A a
        where a.ROWNMBR = @ROW
        execute sp_executesql @String
        set @ROW = @ROW + 1
    end 

#19

Select @pk = @pk + 1 would be better: SET @pk += @pk. Avoid using SELECT if you are not referencing tables are are just assigning values.

选择@pk = @pk + 1更好:设置@pk += @pk。如果不引用表，则避免使用SELECT。

#20

Step1: Below select statement creates a temp table with unique row number for each record.

Step1:下面的select语句为每个记录创建一个具有唯一行号的临时表。

select eno,ename,eaddress,mobno int,row_number() over(order by eno desc) as rno into #tmp_sri from emp 

Step2:Declare required variables

步骤2:申报所需的变量

DECLARE @ROWNUMBER INT
DECLARE @ename varchar(100)

Step3: Take total rows count from temp table

步骤3:从临时表中获取总行数

SELECT @ROWNUMBER = COUNT(*) FROM #tmp_sri
declare @rno int

Step4: Loop temp table based on unique row number create in temp

Step4:基于在temp中创建的惟一行号的循环临时表

while @rownumber>0
begin
  set @rno=@rownumber
  select @ename=ename from #tmp_sri where rno=@rno  **// You can take columns data from here as many as you want**
  set @rownumber=@rownumber-1
  print @ename **// instead of printing, you can write insert, update, delete statements**
end

#21

This approach only requires one variable and does not delete any rows from @databases. I know there are a lot of answers here, but I don't see one that uses MIN to get your next ID like this.

这种方法只需要一个变量，不从@database中删除任何行。我知道这里有很多答案，但是我没有看到一个用MIN来获取下一个ID的例子。

DECLARE @databases TABLE
(
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)

-- insert a bunch rows into @databases

DECLARE @CurrID INT

SELECT @CurrID = MIN(DatabaseID)
FROM @databases

WHILE @CurrID IS NOT NULL
BEGIN

    -- Do stuff for @CurrID

    SELECT @CurrID = MIN(DatabaseID)
    FROM @databases
    WHERE DatabaseID > @CurrID

END