这是我使用的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 

我是这样做的:

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

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

另一种不需要改变模式或使用临时表的方法:

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

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

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

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

你可以使用while循环:

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

    Delete From #TempTable Where x = x
End

如果您别无选择，只能逐行创建FAST_FORWARD游标。它和构建while循环一样快，而且更容易长期维护。

FAST_FORWARD 指定一个启用性能优化的FORWARD_ONLY、READ_ONLY游标。如果还指定了SCROLL或FOR_UPDATE，则FAST_FORWARD不能指定。