create function fNoticeSp(@pcode varchar)
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=@str +b.supplierName from TNoticePermitSupplier a,TSupplierTree b where a.supplierCode=b.supplierCode and a.parPCode=@pcode
return isnull(@str,'无')
End
go如果单条执行:
select b.supplierName from TNoticePermitSupplier a,TSupplierTree b where a.supplierCode=b.supplierCode and a.parPCode='22';
会出现下面这样的两条记录
明伟厂
凯达(俞)但是我想把它合成 "明伟厂,凯达(俞)"这样的字符串,返回字符串,却不行,请问怎样解决,谢了
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=@str +b.supplierName from TNoticePermitSupplier a,TSupplierTree b where a.supplierCode=b.supplierCode and a.parPCode=@pcode
return isnull(@str,'无')
End
go如果单条执行:
select b.supplierName from TNoticePermitSupplier a,TSupplierTree b where a.supplierCode=b.supplierCode and a.parPCode='22';
会出现下面这样的两条记录
明伟厂
凯达(俞)但是我想把它合成 "明伟厂,凯达(俞)"这样的字符串,返回字符串,却不行,请问怎样解决,谢了
returns varchar(8000)
as
begin declare @str varchar(8000) --需要给@str初始值
set @str=''
select @str=@str + ',' + b.supplierName from TNoticePermitSupplier a,TSupplierTree b where a.supplierCode=b.supplierCode and a.parPCode=@pcode
set @str=stuff(@str,1,1,'')
select isnull(@str,'无')
return isnull(@str,'无')
End
go
declare @str varchar(8000)
select @str=@str +b.supplierName from TNoticePermitSupplier a,TSupplierTree b where a.supplierCode=b.supplierCode and a.parPCode=@pcode
select @str
另外少了一个@str=@str +b.supplierName+','
--改
create function fNoticeSp(@pcode varchar)
returns varchar(8000)
as
begin declare @str varchar(8000) --需要给@str初始值
set @str=''
select @str=@str + ',' + b.supplierName from TNoticePermitSupplier a,TSupplierTree b where a.supplierCode=b.supplierCode and a.parPCode=@pcode
set @str=stuff(@str,1,1,'')
return isnull(@str,'无')
End
go
Insert TEST Select 1,N'北京'
Union All Select 1,N'上海'
Union All Select 3,N'南京'
GO
Create Function GetName(@ID Int)
Returns Nvarchar(1000)
As
Begin
Declare @S Nvarchar(1000)
Set @S=''
Select @S=@S+','+Name From TEST Where ID=@ID
Return(Stuff(@S,1,1,''))
End
GO
Select
ID,
dbo.GetName(ID) As Name,
Count(Name) As [Count]
From TEST
Group By ID
GO
Drop Table TEST
Drop Function GetName
create function fNoticeSp(@pcode varchar)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str +','+b.supplierName from TNoticePermitSupplier a,TSupplierTree b where a.supplierCode=b.supplierCode and a.parPCode=@pcode
set @str=stuff(@str,1,1,'')
return isnull(@str,'无')
End
go当我:
select b.supplierName from TNoticePermitSupplier a,TSupplierTree b where a.supplierCode=b.supplierCode and a.parPCode='20060819091839203jbas'
会出现两条记录
但当我
select dbo.fNoticeSp('20060819091839203jbas')
却是出现 "无"了
应该这样申明:
create function fNoticeSp(@pcode varchar(100))
一定要把varchar(100)给指定了,
当然还是有初始值,
谢谢各位了!
--定义的时候一定要指定长度,否则系统认为定义的是varchar(1)
create function fNoticeSp(@pcode varchar(200))