SQLSERVER函数的使用

[oceantang]

在话务员表---staff表 中存在两列数据:一列是staff_id,一列是staffname，数据如下：
staff_id staffname
006987 aaa
006877 bbb
006988 ccc
006979 ddd
006980 eee
006981 fff
006982 ggg
006983 hhh
.... ....

现在我想使用一个函数来实现一个功能：得到的结果是006987,006877,006988,006979,006980,006981,006982,006983................问题是现在staff表中的staff_id和staffname的值不止这些数据。能否通过函数里的游标实现呢？该怎么用函数写呢？这个功能改怎么实现？

[wzy_love_sly]

if object_id('staff') is not null
drop table staff
go
create table staff(staff_id varchar(50),staffname varchar(50))
insert into staff select '006987','aaa'
insert into staff select '006877','bbb'
insert into staff select '006988','ccc'
insert into staff select '006983','ddd'
insert into staff select '006980','eee'
insert into staff select '006981','fff'
insert into staff select '006982','ggg'
insert into staff select '006983','hhh'

go
if object_id('get_staff_id') is not null
drop function get_staff_id
go
create function get_staff_id()--这加参数
returns varchar(8000)
as
begin
declare @r varchar(8000)
select @r=isnull(@r+',','')+staff_id from staff--这加条件
return @r
end
go

select dbo.get_staff_id() as 结果

006987,006877,006988,006983,006980,006981,006982,006983

[sword_88]

[Quote=引用 1 楼 wzy_love_sly 的回复:]
SQL codeif object_id('staff') is not null
drop table staff
go
create table staff(staff_id varchar(50),staffname varchar(50))
insert into staff select '006987','aaa'
insert into staff select '006877','bbb'
insert into staff select '006988','ccc'
insert into staff select '006983','ddd'
insert into staff select '006980','eee'
insert into staff select '006981','fff'
insert into staff selec…
[/Quote]


顶！

[水族杰纶]

declare @S varchar(8000)
declare @staff table (staff_id varchar(50),staffname varchar(50))
insert into @staff select '006987','aaa'
insert into @staff select '006877','bbb'
insert into @staff select '006988','ccc'
insert into @staff select '006983','ddd'
insert into @staff select '006980','eee'
insert into @staff select '006981','fff'
insert into @staff select '006982','ggg'
insert into @staff select '006983','hhh'
select @s=isnull(@s+',','')+staff_id from @staff
select @s

006987,006877,006988,006983,006980,006981,006982,006983

（所影响的行数为 1 行）

[chenjunsheep]

都被大家说完了,我只有帮顶了