Question

sql server 2005 存储过程里定义一个变量取很多值怎么做 :::急

alter proc a
as
declare @ids
select proc_id from LI63_ProCategories order by proc_id desc
print @ids

注：
select proc_id from LI63_ProCategories order by proc_id desc
会取出很多值
怎么做将他们都取出来

Answer 1

1.游标
alter proc a
as
declare @ids
declare c1 cursor for
select proc_id from LI63_ProCategories order by proc_id desc
open c1
fetch next from c1 into @ids
while @@fetch_status=0
begin print @ids fetch next from c1 into @ids end
close c1
deallocate c1

2.字符串组
alter proc a
as
declare @ids varchar(1000)
select @ids=isnull(@ids,'')+convert(varchar,proc_id)+',' from LI63_ProCategories order by proc_id desc
print @ids

Answer 2

select proc_id from LI63_ProCategories order by proc_id desc，这个语句已经将所有的值都取出来了，如果你是想使用变量产生“value1,value2,value3....”这样的格式的话，可以使用下面方法

declare @ids nvarchar(max)
set @ids=''
select @ids=@ids + proc_id +',' from LI63_ProCategories order by proc_id desc
print @ids

Answer 3

alter proc a
as
declare @ids
set @ids=''

select @ids=@ids+','+proc_id from LI63_ProCategories order by proc_id desc
print @ids