create proc p_qry
@pclass int
as
set nocount on
declare @l int
create table #t(pclass int,level int)
set @l=0
insert #t select pclass,@l from Tpclass where fre=@pclass
while @@rowcount>0
begin
set @l=@l+1
insert #t select a.pclass,@l
from Tpclass a,#t b
where a.fre=b.pclass and b.level=@l-1
end
select a.* from Tpclass a,#t b where a.pclass=b.pclass
@pclass int
as
set nocount on
declare @l int
create table #t(pclass int,level int)
set @l=0
insert #t select pclass,@l from Tpclass where fre=@pclass
while @@rowcount>0
begin
set @l=@l+1
insert #t select a.pclass,@l
from Tpclass a,#t b
where a.fre=b.pclass and b.level=@l-1
end
select a.* from Tpclass a,#t b where a.pclass=b.pclass
将 nvarchar 值 ',0,' 转换为数据类型为 int 的列时发生语法错误。应该怎么改阿
classname nvarchar(20) not null,/*产品类别名*/
fre nvarchar(30) null,/*父类*/ --这里是字符?
@pclass int
as
set nocount on
declare @l int
create table #t(pclass int,level int)
set @l=0
insert #t select pclass,@l from Tpclass where charindex(','+cast(@pclass as varchar)+',',','+fre+',')>0
while @@rowcount>0
begin
set @l=@l+1
insert #t select a.pclass,@l
from Tpclass a,#t b
where b.level=@l-1 and charindex(','+cast(b.pclass as varchar)+',',','+a.fre+',')>0
end
select a.* from Tpclass a,#t b where a.pclass=b.pclass
还有你上面的会把 101 和 102 的记录也读出来的
帮我改改哈,那个 fre 就得用 字符型阿
create table Tpclass/*产品类别表*/
(
pclass integer not null,/*产品类别代号*/
classname nvarchar(20) not null,/*产品类别名*/
fre nvarchar(30) null,/*父类*/
)
alter table Tpclass add constraint pk_Tpclass primary key(pclass)
insert Tpclass select 1 ,'asdf' ,',0,'
union all select 101 ,'safdafd' ,',1,'
union all select 10101,'dsfae' ,',101,'
union all select 10102,'safsa' ,',101,'
union all select 102 ,'dsafds' ,',1,'
union all select 10201,'sdfaf' ,',102,'
union all select 10202,'dfjals' ,',102,'
go--查询的存储过程
create proc p_qry
@pclass int
as
set nocount on
declare @l int
create table #t(pclass int,level int)
set @l=0
insert #t select pclass,@l from Tpclass where charindex(','+cast(@pclass as varchar)+',',','+fre+',')>0
while @@rowcount>0
begin
set @l=@l+1
insert #t select a.pclass,@l
from Tpclass a,#t b
where b.level=@l-1 and charindex(','+cast(b.pclass as varchar)+',',','+a.fre+',')>0
end
select a.* from Tpclass a,#t b
where a.pclass=b.pclass
and not exists( --只查最明细的
select * from Tpclass
where charindex(','+cast(b.pclass as varchar)+',',','+fre+',')>0)
go--查询
exec p_qry 1
go--删除测试
drop proc p_qry
drop table Tpclass/*--测试结果pclass classname fre
----------- -------------------- ------------------------------
10101 dsfae ,101,
10102 safsa ,101,
10201 sdfaf ,102,
10202 dfjals ,102,
--*/