--存储过程1
create procedure p1
as
select ID,name from (select [ID]=1,[Name]='name') t
go--存储过程2,调用存储过程1
create procedure p2
as
--创建临时表存储结果集
create table #(ID int,name varchar(10))--插入数据
insert # exec p1--查询数据
select * from #--删除临时表
drop table #go
create procedure p1
as
select ID,name from (select [ID]=1,[Name]='name') t
go--存储过程2,调用存储过程1
create procedure p2
as
--创建临时表存储结果集
create table #(ID int,name varchar(10))--插入数据
insert # exec p1--查询数据
select * from #--删除临时表
drop table #go
cpbm char(10), /*子产品编码*/
sl int,/*构成数量*/
flag char(1) /*标志,Y表明子产品可再细分,N表示不可再细分*/我现在需要得到一个产品的最终构成情况(即把它完全细分到不可细分的产品),我写了一个存储过程,大致内容如下:create procedure p_cpgc /*取产品构成*/
@p_cpbm varchar(10), /*产品编码*/
@p_sl int/*计算数量*/
as
declare @v_cpbm char(10),@v_flag char(1),@v_sl intcreate table #result
(
cpbm char(10) not null,/*产品编码*/
fssl int null/*数量*/
)
declare c1 cursor local for
select cpbm,flag,fssl from css_cpjg where pcpbm=@p_cpbm
open c1
fetch c1 into @v_cpbm,@v_flag,@v_sl
while @@fetch_status=0
begin
if @v_flag='N' /*不可再细分的产品*/
insert #result(cpbm,sl) values(@v_cpbm,@v_sl*@p_sl)
else /*否则,循环调用本过程,取@v_cpbm的构成*/
insert #result execute p_cpgc @p_cpbm=@v_cpbm,@p_sl=@v_sl
fetch c1 into @v_cpbm,@v_flag,@v_sl
end
close c1
deallocate c1
select cpbm,sl=sum(sl) from #result group by cpbm
go当产品的结构只有2层时,不会出错,但如果产品构成层次超过3层,就出错了,执行存储过程的时候,提示insert exec不能嵌套
@p_cpbm varchar(10), /*产品编码*/
@p_sl int/*计算数量*/
asdeclare @result table
(
cpbm char(10) not null, /*产品编码*/
fssl int null /*数量*/
)insert @result(cpbm,sl)
select cpbm,fssl*@p_sl from css_cpjg where pcpbm=@p_cpbm and flag='N'while @@rowcount>0
begin
insert @result(cpbm,sl)
select A.cpbm,A.fssl*@p_sl
from css_cpjg A
join @result B on A.pcpbm=B.cpbm and A.flag='N'
where A.cpbm not in(select cpbm from @result)
endselect * from @result
go
@p_cpbm varchar(10), /*产品编码*/
@p_sl int/*计算数量*/
asdeclare @result table
(
cpbm char(10) not null, /*产品编码*/
fssl int null /*数量*/
)insert @result(cpbm,sl)
select cpbm,fssl*@p_sl from css_cpjg where pcpbm=@p_cpbm and flag='N'while @@rowcount>0
----------------------------------------如果一个产品是全部由可细分的产品构成的,上一句insert语句将导致@@rowcount=0
begin
insert @result(cpbm,sl)
select A.cpbm,A.fssl*@p_sl
from css_cpjg A
join @result B on A.pcpbm=B.cpbm and A.flag='N'
where A.cpbm not in(select cpbm from @result)
endselect * from @result
go
insert @result(cpbm,sl)
select A.cpbm,A.fssl*@p_sl
from css_cpjg A
join @result B on A.pcpbm=B.cpbm and A.flag='N'
where A.cpbm not in(select cpbm from @result)
end===============================>begin
insert @result(cpbm,sl)
select A.cpbm,A.fssl*@p_sl
from css_cpjg A
join @result B on A.pcpbm=B.cpbm and B.flag='N'
where A.cpbm not in(select cpbm from @result)
end
@p_cpbm varchar(10), /*产品编码*/
@p_sl int/*计算数量*/
asdeclare @result table
(
cpbm char(10) not null, /*产品编码*/
fssl int null, /*数量*/
flag varchar(10)
)insert @result(cpbm,sl,flag)
select cpbm,fssl*@p_sl,flag from css_cpjg where pcpbm=@p_cpbm and flag='N'while @@rowcount>0
begin
insert @result(cpbm,sl,flag)
select A.cpbm,A.fssl*@p_sl,A.flag
from css_cpjg A
join @result B on A.pcpbm=B.cpbm and A.flag='N'
where A.cpbm not in(select cpbm from @result)
endselect * from @result
go
select cpbm,fssl*@p_sl,flag from css_cpjg where pcpbm=@p_cpbm and flag='N'”的时候,@@rowcount是0,循环根本进不去pcpbm cpbm sl flag
001 002 2 Y
001 003 3 Y
001 004 7 Y
002 005 2 N
002 006 3 N
003 006 2 N
004 007 2 N
@p_cpbm varchar(10), /*产品编码*/
@p_sl int/*计算数量*/
asdeclare @result table
(
cpbm char(10) not null, /*产品编码*/
fssl int null, /*数量*/
flag varchar(10)
)insert @result(cpbm,sl,flag)
select cpbm,fssl*@p_sl,flag from css_cpjg where pcpbm=@p_cpbm
while @@rowcount>0
begin
insert @result(cpbm,sl,flag)
select A.cpbm,A.fssl*@p_sl,A.flag
from css_cpjg A
join @result B on A.pcpbm=B.cpbm
where B.flag<>'N'
and A.cpbm not in(select cpbm from @result)
endselect * from @result
go