存储过程表的循环

以下以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列进行遍历