Transact-SQL不使用游标的两个循环方法_SQL技巧

TSQL不用游标就可以循环的两个方法：

方法一：

SELECT * INTO #t1 FROM dbo.HorseBase

 

DECLARE @id int

DECLARE @t2 table (id int)

 

WHILE (exists(SELECT HorseNumber FROM #t1))

BEGIN

SELECT TOP 1 @id=HorseNumber FROM #t1

INSERT INTO @t2 SELECT HorseNumber FROM #t1 WHERE HorseNumber=@id

DELETE FROM #t1 WHERE HorseNumber=@id

END

 

DROP Table #t1

方法二：

DECLARE @r int

DECLARE @number int;

DECLARE @t2 table ( [name] nvarchar(200))

 

SELECT ROW_NUMBER() OVER (ORDER BY [Name] ) AS number,[name] INTO #nn1 FROM dbo.tbl_Entity

SELECT @number=MAX(number) FROM #nn1

SET @r=1

WHILE (@r<=@number)

BEGIN

INSERT INTO @t2 SELECT [name] FROM #nn1 WHERE number=@r

SET @r=@r 1

END

 

DROP TABLE #nn1

注：ROW_NUMBER() 在分页存储过程中也是比较好用的。