SQL Server中如何实现遍历表的记录
SQL Server遍历表一般都要用到游标，SQL Server中可以很容易的用游标实现循环，实现SQL Server遍历表中记录。
但游标在实际的开发中都不推荐使用。
我们知道还可以借助临时表或表变量等来实现SQL Server遍历表
下例为用表变量来实现简单的循环:
(直接复制到查询分析器中运行即可)
declare @temp table
(
[id] int IDENTITY(1,1),
[Name] varchar(10)
)
declare @tempId int,@tempName varchar(10)
insert into @temp values('a')
insert into @temp values('b')
insert into @temp values('c')
insert into @temp values('d')
insert into @temp values('e')
select * from @temp
WHILE EXISTS(select [id] from @temp)
begin
SET ROWCOUNT 1
select @tempId = [id],@tempName=[Name] from @temp
SET ROWCOUNT 0
delete from @temp where [id] = @tempId
print 'Name:----'+@tempName
end
附上另外一种sql 测试代码：
declare @temp table (
[id] int IDENTITY(1,1),
[Name] varchar(10) )
declare @tempId int,@tempName varchar(10) ,@tempcount int
insert into @temp values('a')
insert into @temp values('b')
insert into @temp values('c')
insert into @temp values('d')
insert into @temp values('e')
set @tempcount=(select COUNT(*) from @temp);
set @tempId=0;
while (@tempId<=@tempcount )
begin
set @tempName=(select name from @temp where id=@tempId);
print 'the id is :'+str(@tempId) +' the name is : '+@tempName;
set @tempId=@tempId+1;
end
测试结果如下：
the id is : 1 the name is : a
the id is : 2 the name is : b
the id is : 3 the name is : c
the id is : 4 the name is : d
the id is : 5 the name is : e
