set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[P_DJ]
(
@deeplevel int,
@firstcode nvarchar(100)
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
create table #tree (
code nvarchar(100),
deeplevel int,
cbdesc nvarchar(100),
qty_nee numeric(19, 8),
loc varchar(32),
tree nvarchar(max) default ''
)
declare @cbdesc varchar(32),
@QTY_NEED numeric(19,8),
@loc varchar(32)
select top 1 @cbdesc=BOMT.CBDESC,@QTY_NEED=BOMT.QTY_NEED,@loc=MAINBOM.LOC from BOMT INNER JOIN MAINBOM ON BOMT.CODE=MAINBOM.BOMNO where CODE=@firstcodeinsert #tree
select @firstcode,@deeplevel,@cbdesc,@QTY_NEED,@loc,@firstcode
while @@rowcount>0
begin
set @deeplevel=@deeplevel+1 --select top 1 @loc=LOC from MAINBOM WHERE BOMNO=@cbdesc
insert #tree
select BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,@loc,#tree.tree+' '+P_CODE
from BOMT
join #tree
on #tree.deeplevel=@deeplevel-1
and BOMT.P_CODE collate database_default =#tree.code
end
--space((deeplevel-1)*2)+cast(deeplevel as varchar),
select code,cbdesc,qty_nee,loc from #tree
order by tree
DROP TABLE #tree
RETURN @@ERROR
END
SET NOCOUNT OFF
SET XACT_ABORT OFF
为什么LOC这个字段的值只有一个,应该是没个CODE的LOC啊! 我的那里写错了?帮忙看下
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[P_DJ]
(
@deeplevel int,
@firstcode nvarchar(100)
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
create table #tree (
code nvarchar(100),
deeplevel int,
cbdesc nvarchar(100),
qty_nee numeric(19, 8),
loc varchar(32),
tree nvarchar(max) default ''
)
declare @cbdesc varchar(32),
@QTY_NEED numeric(19,8),
@loc varchar(32)
select @cbdesc=BOMT.CBDESC,@QTY_NEED=BOMT.QTY_NEED,@loc=MAINBOM.LOC from BOMT INNER JOIN MAINBOM ON BOMT.CODE=MAINBOM.BOMNO where CODE=@firstcodeinsert #tree
select @firstcode,@deeplevel,@cbdesc,@QTY_NEED,@loc,@firstcode
while @@rowcount>0
begin
set @deeplevel=@deeplevel+1 --select top 1 @loc=LOC from MAINBOM WHERE BOMNO=@cbdesc
insert #tree
select BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,@loc,#tree.tree+' '+P_CODE
from BOMT
join #tree
on #tree.deeplevel=@deeplevel-1
and BOMT.P_CODE collate database_default =#tree.code
end
--space((deeplevel-1)*2)+cast(deeplevel as varchar),
select code,cbdesc,qty_nee,loc from #tree
order by tree
DROP TABLE #tree
RETURN @@ERROR
END
SET NOCOUNT OFF
SET XACT_ABORT OFF