存储过程兑现一个功能

2012-12-31

存储过程实现一个功能set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[P_DJS](@deepl

存储过程实现一个功能


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[P_DJS]
(
@deeplevel int,
@firstcode nvarchar(100)
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN


create table #tree 
(
bomno nvarchar(100),
code nvarchar(100), 
deeplevel int, 
cbdesc nvarchar(100), 
qty_nee numeric(19,8), 
loc varchar(32),
wastage numeric(6,2), 
isLeafnode int,
tree nvarchar(max) default '' 
) 
declare 
@cbdesc varchar(32), 
@QTY_NEED numeric(19,8), 
@loc varchar(32), 
@wastage numeric(6,2)
insert #tree 
select BOMT.BOMNO,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,1,
BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on  BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO=@firstcode 


WHILE @@rowcount > 0 
BEGIN
SET @deeplevel = @deeplevel + 1

update #tree set isLeafnode= 0 from #tree 
    join BOMT
    on #tree.deeplevel=@deeplevel-1
    and BOMT.BOMNO collate database_default =#tree.code
insert #tree 
    select @firstcode,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,1,#tree.tree+'_'+BOMT.CODE+left('00000000000000000000',20-len(BOMT.CODE)) 
    from BOMT
    join #tree
    on #tree.deeplevel=@deeplevel-1
    and BOMT.BOMNO collate database_default =#tree.code
    left join MAINBOM 
    on BOMT.CODE=MAINBOM.BOMNO
END
select space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,wastage,(select top 1 TAXUP from ANT  where ANT.CODE=#tree.code AND TAXUP > 0 order by DATETIME desc) taxup from #tree  
--order by code


RETURN @@ERROR END

SET NOCOUNT OFF
SET XACT_ABORT OFF

想实现这样一个功能在第一条数据的后面加一个字段A，字段A的值是((2807+(2087*(WASTAGE/100)))*BOMT.QTY_NEED)，而它下接数据的字段A的值是它上接数据这个字段A的值做基数来进行计算，就想((上接字段A+(上接字段A*(WASTAGE/100)))*BOMT.QTY_NEED)S
就这样一条一条往下算下接的字段A计算基数是它上接字段A的值，不知道我这样说的明白不？

exec P_DJS 1,'WI-EGD-3575'

1KJ-1111-0030.00
  2ZA1111001020.00
  2ZA1111004030.00
  2ZA1111005020.00
  2ZA1111006010.00
  2ZC1111007010.00
    3COD000003003.00
    3COD000003003.00
    3COD000006003.00
    3COD000006003.00
    3DDD030198000.25
    3EOJ000014001.00
    3EOJ000020001.00
    3EOJ000164001.00
    3JOA010001001.00
    3JOA010003001.00
    3JOA010057001.00
    3JOS000029001.00
    3JOS000121001.00
    3JOS000149001.00
    3PCA000975005.00

    3PCA000980005.00
    3PCA0010080010.00
    3POA000071002.00
    3POA000072002.00
    3POA000076002.00
    3POA000077002.00
    3POA000083002.00
    3POA000084002.00
    3POA000087002.00
    3POA000764002.00
    3POA000765002.00
    3POA000766002.00
    3POA001023002.00
    3POA010973002.00
    3POA010974002.00
    3POA010978002.00
    3POA010979002.00
    3POA011519002.00
    3POA100222002.00
    3POA150982002.00
    3POA151393002.00
    3POB000135001.00
    3POB000135001.00
    3POB000135001.00
    3POB001203001.00
    3POB020016001.00
    3PPS150971000.00
    3PPS150972000.00
    3PPS150981000.00
    3PPS150987000.00
    3PPS151392000.00
    3PPS151394000.00
    3PSA070223000.00
    3WAA000013001.00
    3WBH000015001.00
    3WBL000345001.00
    3WOH000024001.00
    3WOH000044001.00
    3WOH000299001.00
    3WOH000508001.00
    3WOH000525001.00
    3WOH000526001.00
    3WOK000063001.00
    3WOX000007001.00
    3WOX000017001.00
    3ZB1111004000.00
    3ZC1102005030.00
    3ZC1102006040.00
    3ZC1111001010.00
    3ZP1111007001.00
      4MOD000016000.25
      4PMA010049003.00
      4PMA010049003.00
      4PMA010049003.00
      4PMA030001003.00
      4PMA030001003.00
      4PMA050001003.00
      4PMA050001003.00
      4PMA050001003.00
      4PMA050001003.00
      4PMA050001003.00
      4PMA050001003.00
      4PMA050001003.00
      4PMA050001003.00
      4PMA050001003.00
      4PMA050001003.00
      4PMA060011003.00
      4PMA090001003.00
      4PMA090001003.00
      4PMA100004003.00
      4PMB000066003.00
      4PMB000066003.00
      4PMB000434003.00
      4PMB000538003.00
      4PMB000538003.00
      4POA000073002.00
      4POA000079002.00

      4POA000082002.00
      4POA000975002.00
      4POA000980002.00
      4POA001008002.00
      4POA030915002.00
      4POA070223002.00
      4POA210081002.00
      4POB010072001.00
      4POB010073001.00
      4POB140015001.00
      4PPA1509710015.00
      4PPA1509720015.00
      4PPA1509810015.00
      4PPA1509870015.00
      4PPA1513920015.00
      4PPA1513940015.00
      4PSA111017000.00
      4WBH000003001.00
      4WOH000345001.00
      4WOO600007000.00
      4ZC1111004000.00
        5COA000058003.00
        5COA000058003.00
        5COA000058003.00
        5COA000058003.00
        5COA000058003.00
        5COA000058003.00
        5MZB010001000.75
        5MZB030001000.75
        5MZB040057000.75
        5MZC010001000.75
        5MZC030001000.75
        5MZC040001000.75
        5MZC060002000.75
        5MZC110001000.75
        5MZE010022000.75
        5MZE020011000.75
        5MZE020012000.75
        5MZE030001000.75
        5MZE040015000.75
        5MZE040016000.75
        5MZE060003000.75
        5MZM010011000.75
        5MZM020030000.75
        5MZM040003000.75
        5MZM140001000.75
        5MZM150001000.75
        5MZM180005000.00
        5MZM190005000.75
        5MZM210003000.00
        5MZM270014000.75
        5MZM270020000.75
        5MZM270024000.75
        5MZM270029000.75
        5MZM290001000.75
        5MZP010035000.75
        5MZP010038000.75
        5MZP020001000.75
        5MZP030038000.75
        5MZP040017000.75

        5MZP140001000.75
        5PMA010020003.00
        5PMA010020003.00
        5PMA010020003.00
        5PMA040001003.00
        5PMA050001003.00
        5PMA050001003.00
        5PMA060011003.00
        5PMA100004003.00
        5PMA130004003.00
        5PMB000335003.00
        5PMB000606003.00
        5POA111017002.00
        5POA150971002.00
        5POA150972002.00
        5POA150981002.00
        5POA150987002.00
        5POA151392002.00
        5POA151394002.00
        5WOH000023001.00
        5WOH000295001.00
        5WOH000298001.00
        5WOH000320001.00
        5WOH000523001.00
          6MZM090002000.75
          6MZM090003000.75
          6MZM110002000.75
          6MZM170003000.75
          6MZM210002000.75
          6MZM320005000.75
          6PMA010049003.00
          6PMA010049003.00
          6PMA010049003.00
          6PMA010049003.00
          6PMA010049003.00
          6PMA010049003.00
          6PMA100004003.00
          6PMA130004003.00
          6POA000078002.00
          6WOH000021001.00
          6WOO300022000.00
          6WOO320002000.00
          6WOO400037000.00
          6WOO400049000.00
          6WOO400069000.00
          6WOO400072000.00
            7PMA050001003.00

[解决办法]
不明白。
[解决办法]
木有看懂存储过程兑现一个功能

热点排行

SQL Server

存储过程兑现一个功能