--创建函数
CREATE function f_str(@docid varchar(50))
returns varchar(8000)
as
begin
declare @tmp varchar(8000)
set @tmp=''
select @tmp=@tmp+','+KeyName from keyword where keyId in (select keyId from docInKey where docid=@docid)
return stuff(@tmp,1,1,'')
end
go--调用
select *,
[keyword]=dbo.f_str(docid)
from document
CREATE function f_str(@docid varchar(50))
returns varchar(8000)
as
begin
declare @tmp varchar(8000)
set @tmp=''
select @tmp=@tmp+','+KeyName from keyword where keyId in (select keyId from docInKey where docid=@docid)
return stuff(@tmp,1,1,'')
end
go--调用
select *,
[keyword]=dbo.f_str(docid)
from document
CREATE function f_str(@docid varchar(50))
returns varchar(8000)
as
begin
declare @tmp varchar(8000)
set @tmp=''
select @tmp=@tmp+'、'+KeyName from keyword where keyId in (select keyId from docInKey where docid=@docid)
if @tmp is null
return ''
return stuff(@tmp,1,1,'')
end
go--调用
select *,
[keyword]=dbo.f_str(docid)
from document
create function f_str(@docId int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r = ''
select
@r = @r + ',' + b.[keyName]
from
[docInKey] a,
[keyword] b
where
a.keyId = b.keyId
and
a.docId = @docId
set @r = stuff(@r,1,1,'')
return @r
end--执行查询
select
DocId,
title,
[keyword] = dbo.f_str(DocId)
from
[document]
CREATE function f_str(@docid varchar(50))
returns varchar(8000)
as
begin
declare @tmp varchar(8000)
set @tmp=''
select @tmp=@tmp+','+KeyName from keyword where keyId=@docid
return stuff(@tmp,1,1,'')
end
go--调用
select *,
[keyword]=dbo.f_str(docid)
from document