create function fSpArea(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
declare @tpstr varchar(1000)
set @str=''
set @tpstr='无'
while(@tpstr is not null)
begin
select @tpstr=groupData from vSupplierArea where pkey=@id
select @id=parKey from vSupplierArea where pkey=@id
if(@str='') begin
set @str=@tpstr
end
else begin
set @str=@tpstr+'.'+@str
end
end
return (@str)
End
go
vSupplierArea 的表结构:
pkey parkey groupData
10 1 中国
11 10 械玫地
12 11 缥玫
13 11 外车当我有select dbo.fSpArea(13)会死循环
我想得到的结果是: 中国.械玫地.外车谢谢了。
returns varchar(8000)
as
begin
declare @str varchar(8000)
declare @tpstr varchar(1000)
set @str=''
set @tpstr='无'
while(@tpstr is not null)
begin
select @tpstr=groupData from vSupplierArea where pkey=@id
select @id=parKey from vSupplierArea where pkey=@id
if(@str='') begin
set @str=@tpstr
end
else begin
set @str=@tpstr+'.'+@str
end
end
return (@str)
End
go
vSupplierArea 的表结构:
pkey parkey groupData
10 1 中国
11 10 械玫地
12 11 缥玫
13 11 外车当我有select dbo.fSpArea(13)会死循环
我想得到的结果是: 中国.械玫地.外车谢谢了。
insert into vSupplierArea select 10,1 ,'中国'
insert into vSupplierArea select 11,10,'械玫地'
insert into vSupplierArea select 12,11,'缥玫'
insert into vSupplierArea select 13,11,'外车'
gocreate function fSpArea(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=groupData,@id=parkey from vSupplierArea where pkey=@id
while @@rowcount<>0
begin
select @str=groupData+'.'+@str,@id=parkey from vSupplierArea where pkey=@id
end
return @str
End
goselect dbo.fSpArea(13)
/*
-------------------------
中国.械玫地.外车
*/
godrop function fSpArea
drop table vSupplierArea
insert into vSupplierArea select 10,1 ,'中国'
insert into vSupplierArea select 11,10,'械玫地'
insert into vSupplierArea select 12,11,'缥玫'
insert into vSupplierArea select 13,11,'外车'
gocreate function fSpArea(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=groupData,@id=parkey from vSupplierArea where pkey=@id
while @@rowcount<>0
begin
select @str=groupData+'.'+@str,@id=parkey from vSupplierArea where pkey=@id
end
return isnull(@str,'无')
End
goselect dbo.fSpArea(13)
/*
-------------------------
中国.械玫地.外车
*/
godrop function fSpArea
drop table vSupplierArea
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'.'+groupdata from vSupplierArea a where pkey<=@id
and exists(select 1 from vSupplierArea where pkey<=@id group by parkey=a.parkey and max(pkey)=a.pkey)
set @str=substring(@str,2,8000)
return (@str)
End
go
create table vSupplierArea(pkey int,parkey int,groupData varchar(20))
insert into vSupplierArea select 10,1 ,'中国'
insert into vSupplierArea select 11,10,'械玫地'
insert into vSupplierArea select 12,11,'缥玫'
insert into vSupplierArea select 13,11,'外车'
gocreate function fSpArea(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'.'+groupdata from vSupplierArea a where pkey<=@id
and exists(select 1 from vSupplierArea where pkey<=@id group by parkey having parkey=a.parkey and max(pkey)=a.pkey)
set @str=substring(@str,2,8000)
return (@str)
End
go
goselect dbo.fSpArea(13)
/*
-------------------------
中国.械玫地.外车
*/
godrop function fSpArea
drop table vSupplierArea
最后用了libin_ftsafe(子陌红尘)的方法,
pengdali(璇玑的钻石)老大的有点复杂了
declare @s varchar(100)
select @s=groupData from vSupplierArea where pkey=12
if(@s is not null)begin
print 'a'
end
else
begin
print 'b'
end
在查询分析器中,执行上面一段,如果没有pkey=12的值,会打出b,则否会打出a,说明这个可以用,但为什么上面的函数中不能用,且我用if(@tpstr is null) break;也不行,请指教一下了
谢谢!
当条件不成立,没有执行赋值,不代表给了null值,@id原来是几,还是几
我下面给@s付了初值,当条件不成立时,它是不会改变的,谢谢了!declare @s varchar(100)
set @s='ok'
select @s=groupData from vSupplierArea where pkey=11000
if(@s is not null)begin
print 'a'
end
else
begin
print 'b'
end