存储过程表的循环
以下以SQL Server 2000中的NorthWind数据库中的Customers表为例,
用 临时表 + While循环 的方法, 对Customers表中的CompanyName列进行遍历
create table #temp
(
id int identity(1,1),
customer nvarchar(50)
)
declare @customer nvarchar(50)
declare @n int
declare @rows int
select @n=1
insert #temp(customer) select distinct companyname from customers
select @rows = @@rowcount
while @n <= @rows
begin
select @customer = companyname
from customers
where companyname=(select customer from #temp where id = @n)
order by companyname desc
print(@customer)
select @n = @n + 1
end
运行后, 输出结果如下:
（所影响的行数为 91 行）
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Around the Horn
Berglunds snabbk?p
Blauer See Delikatessen
Blondesddsl père et fils
Bólido Comidas preparadas
Bon app'
Bottom-Dollar Markets
B's Beverages
Cactus Comidas para llevar
Centro comercial Moctezuma
Chop-suey Chinese
Comércio Mineiro
Consolidated Holdings
Die Wandernde Kuh
Drachenblut Delikatessen
Du monde entier
Eastern Connection
Ernst Handel
Familia Arquibaldo
FISSA Fabrica Inter. Salchichas S.A.
Folies gourmandes
Folk och f? HB
France restauration
Franchi S.p.A.
Frankenversand
Furia Bacalhau e Frutos do Mar
Galería del gastrónomo
Godos Cocina Típica
Gourmet Lanchonetes
Great Lakes Food Market
GROSELLA-Restaurante
...... (以下略) ..................................
SQL查询遍历数据方法二 [ 游标 + While循环] 以下以SQL Server 2000中的NorthWind数据库中的Customers表为例,
用 游标(Cursor) + While循环 的方法, 对Customers表中的CompanyName列进行遍历
declare @customer nvarchar(50)
declare pcurr cursor for
select distinct companyname from customers
open pcurr
fetch next from pcurr into @customer
while (@@fetch_status = 0)
begin
print (@customer)
fetch next from pcurr into @customer
end
close pcurr
deallocate pcurr
运行后, 输出结果如下:
（所影响的行数为 91 行）
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Around the Horn
Berglunds snabbk?p
Blauer See Delikatessen
Blondesddsl père et fils
Bólido Comidas preparadas
Bon app'
Bottom-Dollar Markets
B's Beverages
Cactus Comidas para llevar
Centro comercial Moctezuma
Chop-suey Chinese
Comércio Mineiro
Consolidated Holdings
Die Wandernde Kuh
Drachenblut Delikatessen
Du monde entier
Eastern Connection
Ernst Handel
Familia Arquibaldo
FISSA Fabrica Inter. Salchichas S.A.
Folies gourmandes
Folk och f? HB
France restauration
Franchi S.p.A.
Frankenversand
Furia Bacalhau e Frutos do Mar
Galería del gastrónomo
Godos Cocina Típica
Gourmet Lanchonetes
Great Lakes Food Market
GROSELLA-Restaurante
...... (以下略) ..................................