MSSQL2000表 单据中自动增加序号

Question

MSSQL2000表 单据中自动增加序号
表E_ORDERD和表E_ORDERDP  (主从表结构)
条件E_ORDERD.BILLID=E_ORDERDP表E_ORDERD
BILLID ,ITEMNO,QTY
1001,    NULL,  123
1001,    NULL,  1234
1001,    NULL,  1212
1001,    2   ,  30
1001,    3   ,  40
1003,    null , 40
1003,    null , 40表E_ORDERDP
BILLID ,ITEMNO,QTY
1001,    NULL,  1
1001,    NULL,  3
1001,    NULL,  4
1001,    2   ,  6
1001,    3   ,  8
1003,    2   , 43
1003,    null , 44
按照单据录入后按录入顺序更新ITEMNO
结果如下：
表E_ORDERD
BILLID ,ITEMNO,QTY
1001,    1,  123
1001,    2,  1234
1001,    3,  1212
1001,    4   ,  30
1001,    5   ,  40
1003,    1   , 40
1003,    2  , 40表E_ORDERDP
BILLID ,ITEMNO,QTY
1001,    1,  1
1001,    2,  3
1001,    3,  4
1001,    4   ,  6
1001,    5   ,  8
1003,    1   , 43
1003,    2 , 44

Answer 1

declare @num int,@id varchar(10)
set @num=0
update E_ORDERDP set ITEMNO=@num,@id=BILLID,@num=case when BILLID=@id then @num+1 else 1 end

Answer 2

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#E_ORDERD') is null
drop table #E_ORDERD
Go
Create table #E_ORDERD([BILLID] int,[ITEMNO] int,[QTY] int)
Insert #E_ORDERD
select 1001,null,123 union all
select 1001,null,1234 union all
select 1001,null,1212 union all
select 1001,2,30 union all
select 1001,3,40 union all
select 1003,null,40 union all
select 1003,null,40
Go
declare @BILLID int,@i intupdate #E_ORDERD
set [ITEMNO]=@i,@i=case when [BILLID]=@BILLID then @i+1 else 1 end,@BILLID=[BILLID]
Select * from #E_ORDERD
/*
BILLID ITEMNO QTY
1001 1 123
1001 2 1234
1001 3 1212
1001 4 30
1001 5 40
1003 1 40
1003 2 40
*/

Answer 3

另一个表也用相同的方法，楼主的数据显示BILLID需要连续，不连续时，需要新一个标识列处理

Answer 4

用标识列方法--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#E_ORDERD') is null
drop table #E_ORDERD
Go
Create table #E_ORDERD([BILLID] int,[ITEMNO] int,[QTY] int)
Insert #E_ORDERD
select 1001,null,123 union all
select 1001,null,1234 union all
select 1001,null,1212 union all
select 1001,2,30 union all
select 1001,3,40 union all
select 1003,null,40 union all
select 1003,null,40
Go
alter table #E_ORDERD add ID int identity
go
update a
set [ITEMNO]=(select COUNT(1) from #E_ORDERD where [BILLID]=a.[BILLID] and ID<=a.ID)
from #E_ORDERD as a
go
alter table #E_ORDERD drop column ID
go
Select * from #E_ORDERD
/*
BILLID ITEMNO QTY
1001 1 123
1001 2 1234
1001 3 1212
1001 4 30
1001 5 40
1003 1 40
1003 2 40
*/

Answer 5

这里的set是按什么顺序执行的

Answer 6

同一单据BILLID,根据里面录入内容明细排序ITEMNO,请问有没有更好或更简单的方法??

Answer 7

要求每个单据BILLID自动排序里面的明细ITEMNO,有没有更好或更简单的方法??

Answer 8

我在触发器里面加入这些脚本后,为什么是最后一个总是没有ITEMNO顺序号的?不解update a
set [ITEMNO]=(select COUNT(1) from E_ORDERD where [BILLID]=a.[BILLID] and ID<=a.ID
and  billid=@new_billid   )
from E_ORDERD as a   and  a.billid=@new_billid  ---单据录入时的BILLID

Answer 9

有的
alter table E_ORDERD add ID int identity

Answer 10

触发器
CREATE TRIGGER [dbo].[E_ORDERD_BI_MDSYNCHRONIZE] ON [dbo].[E_ORDERD]
INSTEAD OF INSERT
AS
SET NOCOUNT ONDECLARE @NEW_USERDEF20 T_T_USERDEF, @NEW_USERDEF19 T_T_USERDEF, @NEW_USERDEF18 T_T_USERDEF, @NEW_USERDEF17 T_T_USERDEF, @NEW_USERDEF16 T_T_USERDEF, @NEW_USERDEF15 T_T_USERDEF, @NEW_USERDEF14 T_T_USERDEF, @NEW_USERDEF13 T_T_USERDEF, @NEW_USERDEF12 T_T_USERDEF, @NEW_USERDEF11 T_T_USERDEF, @NEW_BILLID INT, @NEW_ITEMNO INT, @NEW_GOODSID INT, @NEW_QTY T_T_QUANTITY, @NEW_CANCELED T_T_BOOLEAN, @NEW_UNITID T_T_KEYID,     @NEW_UNITQTY T_T_QUANTITY, @NEW_PRICE T_T_PRICE, @NEW_UNITPRICE FLOAT, @NEW_EXEQTY T_T_QUANTITY, @NEW_EXEDRAWQTY T_T_QUANTITY,     @NEW_AUSERNO INT, @NEW_STOPED INT, @NEW_USERDEF1 T_T_USERDEF, @NEW_USERDEF2 T_T_USERDEF, @NEW_USERDEF3 T_T_USERDEF,     @NEW_USERDEF4  VARCHAR(255), @NEW_INTERCONTROL INT,@NEW_USERDEF5 VARCHAR(255),@NEW_USERDEF6 VARCHAR (255),@NEW_USERDEF7 VARCHAR (255),    @NEW_USERDEF8 VARCHAR (255),@NEW_USERDEF9 VARCHAR (255), @NEW_USERDEF10 VARCHAR (255),@NEW_SEQUENCE INT,@NEW_IORDERNO INT,     @NEW_BOMID INT , @NVENDORID  INT DECLARE NEWED CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR SELECT BILLID, ITEMNO, GOODSID, QTY, CANCELED, UNITID, UNITQTY, PRICE,     UNITPRICE, EXEQTY, EXEDRAWQTY, AUSERNO, STOPED, USERDEF1, USERDEF2, USERDEF3, USERDEF4, INTERCONTROL,USERDEF5,USERDEF6,USERDEF7,    USERDEF8,USERDEF9,USERDEF10,SEQUENCE ,IORDERNO, BOMID,USERDEF11 ,USERDEF12 ,USERDEF13 ,USERDEF14 ,USERDEF15 ,USERDEF16 ,USERDEF17 ,USERDEF18 ,USERDEF19 ,USERDEF20 FROM INSERTEDOPEN NEWEDFETCH NEXT FROM NEWED INTO @NEW_BILLID, @NEW_ITEMNO, @NEW_GOODSID, @NEW_QTY, @NEW_CANCELED, @NEW_UNITID, @NEW_UNITQTY, @NEW_PRICE,     @NEW_UNITPRICE, @NEW_EXEQTY, @NEW_EXEDRAWQTY, @NEW_AUSERNO, @NEW_STOPED, @NEW_USERDEF1, @NEW_USERDEF2, @NEW_USERDEF3, @NEW_USERDEF4,     @NEW_INTERCONTROL,@NEW_USERDEF5,@NEW_USERDEF6,@NEW_USERDEF7,@NEW_USERDEF8,@NEW_USERDEF9,@NEW_USERDEF10 ,@NEW_SEQUENCE,@NEW_IORDERNO,    @NEW_BOMID,@NEW_USERDEF11 
,@NEW_USERDEF12 
,@NEW_USERDEF13 
,@NEW_USERDEF14 
,@NEW_USERDEF15 
,@NEW_USERDEF16 
,@NEW_USERDEF17 
,@NEW_USERDEF18 
,@NEW_USERDEF19 
,@NEW_USERDEF20 
WHILE (@@FETCH_STATUS = 0)  BEGIN    BEGIN TRANSACTION    SELECT   @NVENDORID=VENDORID     FROM   E_ORDER   WHERE  BILLID=@NEW_BILLID
    IF  @NVENDORID=438
       BEGIN
         SELECT  @NEW_USERDEF1=V.USERDEF1, @NEW_USERDEF2=V.USERDEF2 ,  @NEW_USERDEF3=V.USERDEF3 , @NEW_USERDEF4=V.USERDEF4
              FROM   R_E_ORDERDVIP201(@NEW_GOODSID )  V 
       END
    SELECT @NEW_CANCELED = CANCELED, @NEW_AUSERNO = AUSERNO, @NEW_STOPED = STOPED FROM E_ORDER WHERE BILLID = @NEW_BILLID------加进入的
update E_ORDERD
set  @NEW_ITEMNO=(select COUNT(1) from E_ORDERD where BILLID=a.BILLID and ID<=a.ID  )
from E_ORDERD  a   where  a.billid=@new_billid
    INSERT INTO E_ORDERD (BILLID, ITEMNO, GOODSID, QTY, CANCELED, UNITID, UNITQTY, PRICE, UNITPRICE, EXEQTY, EXEDRAWQTY, AUSERNO, STOPED,         USERDEF1, USERDEF2, USERDEF3, USERDEF4, INTERCONTROL,USERDEF5,USERDEF6,USERDEF7,USERDEF8,USERDEF9,USERDEF10,SEQUENCE,IORDERNO,        BOMID,USERDEF11,USERDEF12,USERDEF13,USERDEF14,USERDEF15,USERDEF16,USERDEF17,USERDEF18,USERDEF19,USERDEF20)
     SELECT @NEW_BILLID, @NEW_ITEMNO, @NEW_GOODSID, @NEW_QTY, @NEW_CANCELED, @NEW_UNITID, @NEW_UNITQTY, @NEW_PRICE, @NEW_UNITPRICE,         @NEW_EXEQTY, @NEW_EXEDRAWQTY, @NEW_AUSERNO, @NEW_STOPED, @NEW_USERDEF1, @NEW_USERDEF2, @NEW_USERDEF3, @NEW_USERDEF4,         @NEW_INTERCONTROL,@NEW_USERDEF5,@NEW_USERDEF6,@NEW_USERDEF7,@NEW_USERDEF8,@NEW_USERDEF9,@NEW_USERDEF10 ,@NEW_SEQUENCE,        @NEW_IORDERNO, @NEW_BOMID    ,@NEW_USERDEF11 
,@NEW_USERDEF12 
,@NEW_USERDEF13 
,@NEW_USERDEF14 
,@NEW_USERDEF15 
,@NEW_USERDEF16,@NEW_USERDEF17
,@NEW_USERDEF18 
,@NEW_USERDEF19 
,@NEW_USERDEF20 
FETCH NEXT FROM NEWED INTO @NEW_BILLID, @NEW_ITEMNO, @NEW_GOODSID, @NEW_QTY, @NEW_CANCELED, @NEW_UNITID, @NEW_UNITQTY, @NEW_PRICE,         @NEW_UNITPRICE, @NEW_EXEQTY, @NEW_EXEDRAWQTY, @NEW_AUSERNO, @NEW_STOPED, @NEW_USERDEF1, @NEW_USERDEF2, @NEW_USERDEF3,         @NEW_USERDEF4, @NEW_INTERCONTROL,@NEW_USERDEF5,@NEW_USERDEF6,@NEW_USERDEF7,@NEW_USERDEF8,@NEW_USERDEF9,@NEW_USERDEF10 ,        @NEW_SEQUENCE,@NEW_IORDERNO, @NEW_BOMID    ,@NEW_USERDEF11 
,@NEW_USERDEF12 
,@NEW_USERDEF13 
,@NEW_USERDEF14 
,@NEW_USERDEF15 
,@NEW_USERDEF16 
,@NEW_USERDEF17 
,@NEW_USERDEF18 
,@NEW_USERDEF19 
,@NEW_USERDEF20 
IF @@TRANCOUNT > 0      COMMIT TRAN   ENDCLOSE NEWEDDEALLOCATE NEWEDSET NOCOUNT OFF
GO

Answer 11

触发器2
CREATE TRIGGER [dbo].[E_ORDERD_BU_EXEEQUALQTY] ON [dbo].[E_ORDERD]
INSTEAD OF UPDATE
AS
SET NOCOUNT ON  DECLARE @NEW_USERDEF20 T_T_USERDEF,@OLD_USERDEF20 T_T_USERDEF, @NEW_USERDEF19 T_T_USERDEF,@OLD_USERDEF19 T_T_USERDEF, @NEW_USERDEF18 T_T_USERDEF,@OLD_USERDEF18 T_T_USERDEF, @NEW_USERDEF17 T_T_USERDEF,@OLD_USERDEF17 T_T_USERDEF, @NEW_USERDEF16 T_T_USERDEF,@OLD_USERDEF16 T_T_USERDEF, @NEW_USERDEF15 T_T_USERDEF,@OLD_USERDEF15 T_T_USERDEF, @NEW_USERDEF14 T_T_USERDEF,@OLD_USERDEF14 T_T_USERDEF, @NEW_USERDEF13 T_T_USERDEF,@OLD_USERDEF13 T_T_USERDEF, @NEW_USERDEF12 T_T_USERDEF,@OLD_USERDEF12 T_T_USERDEF, @NEW_USERDEF11 T_T_USERDEF,@OLD_USERDEF11 T_T_USERDEF, @fQtyZero FLOAT, @fAmtZero FLOAT  DECLARE @NEW_BILLID INT, @NEW_ITEMNO INT, @NEW_GOODSID INT, @NEW_QTY T_T_QUANTITY, @NEW_CANCELED T_T_BOOLEAN, @NEW_UNITID T_T_KEYID,       @NEW_UNITQTY T_T_QUANTITY, @NEW_PRICE T_T_PRICE, @NEW_UNITPRICE FLOAT, @NEW_EXEQTY T_T_QUANTITY, @NEW_EXEDRAWQTY T_T_QUANTITY,       @NEW_AUSERNO INT, @NEW_STOPED INT, @NEW_USERDEF1 T_T_USERDEF, @NEW_USERDEF2 T_T_USERDEF, @NEW_USERDEF3 T_T_USERDEF,       @NEW_USERDEF4  VARCHAR(255), @NEW_INTERCONTROL INT,@NEW_USERDEF5 VARCHAR(255), @NEW_USERDEF6 VARCHAR (255),      @NEW_USERDEF7 VARCHAR (255), @NEW_USERDEF8 VARCHAR (255),@NEW_USERDEF9 VARCHAR (255), @NEW_USERDEF10 VARCHAR (255),       @NEW_IORDERNO INT,@NEW_SEQUENCE INT, @NEW_BOMID INT  DECLARE @OLD_BILLID INT, @OLD_ITEMNO INT, @OLD_GOODSID INT, @OLD_QTY T_T_QUANTITY, @OLD_CANCELED T_T_BOOLEAN, @OLD_UNITID T_T_KEYID,       @OLD_UNITQTY T_T_QUANTITY, @OLD_PRICE T_T_PRICE, @OLD_UNITPRICE FLOAT, @OLD_EXEQTY T_T_QUANTITY, @OLD_EXEDRAWQTY T_T_QUANTITY,       @OLD_AUSERNO INT, @OLD_STOPED INT, @OLD_USERDEF1 T_T_USERDEF, @OLD_USERDEF2 T_T_USERDEF, @OLD_USERDEF3 T_T_USERDEF,       @OLD_USERDEF4  VARCHAR(255), @OLD_INTERCONTROL INT,@OLD_SEQUENCE INT, @OLD_BOMID INT  DECLARE NEWED CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR SELECT BILLID, ITEMNO, GOODSID, QTY, CANCELED, UNITID, UNITQTY, PRICE,       UNITPRICE, EXEQTY, EXEDRAWQTY, AUSERNO, STOPED, USERDEF1, USERDEF2, USERDEF3, USERDEF4, INTERCONTROL,USERDEF5,USERDEF6, USERDEF7,      USERDEF8,USERDEF9,USERDEF10 ,IORDERNO,SEQUENCE, BOMID   ,USERDEF11 ,USERDEF12 ,USERDEF13 ,USERDEF14 ,USERDEF15 ,USERDEF16 ,USERDEF17 ,USERDEF18 ,USERDEF19 ,USERDEF20 FROM INSERTED  DECLARE OLDED CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR SELECT BILLID, ITEMNO, GOODSID, QTY, CANCELED, UNITID, UNITQTY, PRICE,       UNITPRICE, EXEQTY, EXEDRAWQTY, AUSERNO, STOPED, USERDEF1, USERDEF2, USERDEF3, USERDEF4, INTERCONTROL,SEQUENCE, BOMID   ,USERDEF11 ,USERDEF12 ,USERDEF13 ,USERDEF14 ,USERDEF15 ,USERDEF16 ,USERDEF17 ,USERDEF18 ,USERDEF19 ,USERDEF20 FROM DELETED  OPEN NEWED  OPEN OLDED  FETCH NEXT FROM NEWED INTO @NEW_BILLID, @NEW_ITEMNO, @NEW_GOODSID, @NEW_QTY, @NEW_CANCELED, @NEW_UNITID, @NEW_UNITQTY, @NEW_PRICE,       @NEW_UNITPRICE, @NEW_EXEQTY, @NEW_EXEDRAWQTY, @NEW_AUSERNO, @NEW_STOPED, @NEW_USERDEF1, @NEW_USERDEF2, @NEW_USERDEF3, @NEW_USERDEF4,       @NEW_INTERCONTROL,@NEW_USERDEF5, @NEW_USERDEF6,@NEW_USERDEF7,@NEW_USERDEF8,@NEW_USERDEF9, @NEW_USERDEF10 ,@NEW_IORDERNO,@NEW_SEQUENCE,      @NEW_BOMID  ,@NEW_USERDEF11 
,@NEW_USERDEF12 
,@NEW_USERDEF13 
,@NEW_USERDEF14 
,@NEW_USERDEF15 
,@NEW_USERDEF16 
,@NEW_USERDEF17 
,@NEW_USERDEF18 
,@NEW_USERDEF19 
,@NEW_USERDEF20 
IF (@@FETCH_STATUS = 0)    FETCH NEXT FROM OLDED INTO @OLD_BILLID, @OLD_ITEMNO, @OLD_GOODSID, @OLD_QTY, @OLD_CANCELED, @OLD_UNITID, @OLD_UNITQTY, @OLD_PRICE,         @OLD_UNITPRICE, @OLD_EXEQTY, @OLD_EXEDRAWQTY, @OLD_AUSERNO, @OLD_STOPED, @OLD_USERDEF1, @OLD_USERDEF2, @OLD_USERDEF3,         @OLD_USERDEF4, @OLD_INTERCONTROL,@OLD_SEQUENCE, @OLD_BOMID  ,@OLD_USERDEF11 
,@OLD_USERDEF12 
,@OLD_USERDEF13 
,@OLD_USERDEF14 
,@OLD_USERDEF15 
,@OLD_USERDEF16 
,@OLD_USERDEF17 
,@OLD_USERDEF18 
,@OLD_USERDEF19 
,@OLD_USERDEF20 
WHILE (@@FETCH_STATUS= 0)  BEGIN      BEGIN TRANSACTION  
    /**
    IF (@NEW_USERDEF10 NOT IN  ('正品','返镀','脱铬','收发') OR @NEW_USERDEF10 IS NULL)
BEGIN
RAISERROR('请录入正确的货品状态!!%d',16, 1,@NEW_GOODSID)
ROLLBACK
RETURN
END
**/      IF  @NEW_EXEDRAWQTY>0
    BEGIN
       IF    @OLD_GOODSID<>@NEW_GOODSID
      BEGIN
     RAISERROR('存在对应委外领料单!%d',16, 1,@new_goodsid)
        ROLLBACK
        RETURN
      END
    END
    
    IF (@NEW_GOODSID <> @OLD_GOODSID AND @NEW_INTERCONTROL = 1)        BEGIN          IF (EXISTS (SELECT BILLID FROM E_ORDER WHERE GOODSID = @NEW_GOODSID AND BILLID = @NEW_BILLID))            BEGIN        RAISERROR('主从货品不能相同!',16, 1)              ROLLBACK              RETURN            END          SET @NEW_INTERCONTROL = 0        END      IF (@OLD_CANCELED = 0 AND @NEW_CANCELED = 0)        BEGIN          SELECT @FQTYZERO = FQTYZERO, @FAMTZERO = FAMTZERO FROM GETZERO()          BEGIN            IF (@OLD_EXEQTY <> @NEW_EXEQTY AND ABS (@NEW_EXEQTY - @NEW_QTY) <= @FQTYZERO)              SET @NEW_EXEQTY = @NEW_QTY            IF (@OLD_EXEDRAWQTY <> @NEW_EXEDRAWQTY AND ABS (@NEW_EXEDRAWQTY - @NEW_QTY) <= @FQTYZERO)              SET @NEW_EXEDRAWQTY = @NEW_QTY            END        END  ------加进入的
update E_ORDERD
set @NEW_ITEMNO=(select COUNT(1) from E_ORDERD where BILLID=a.BILLID and ID<=a.ID  )
from E_ORDERD  a   where  a.billid=@new_billid
    UPDATE E_ORDERD SET USERDEF20=@NEW_USERDEF20, USERDEF19=@NEW_USERDEF19, USERDEF18=@NEW_USERDEF18, USERDEF17=@NEW_USERDEF17, USERDEF16=@NEW_USERDEF16, USERDEF15=@NEW_USERDEF15, USERDEF14=@NEW_USERDEF14, USERDEF13=@NEW_USERDEF13, USERDEF12=@NEW_USERDEF12, USERDEF11=@NEW_USERDEF11, BILLID = @NEW_BILLID, ITEMNO = @NEW_ITEMNO, GOODSID = @NEW_GOODSID, QTY = @NEW_QTY, CANCELED = @NEW_CANCELED,           UNITID = @NEW_UNITID, UNITQTY = @NEW_UNITQTY, PRICE = @NEW_PRICE, UNITPRICE = @NEW_UNITPRICE, EXEQTY = @NEW_EXEQTY,           EXEDRAWQTY = @NEW_EXEDRAWQTY, AUSERNO = @NEW_AUSERNO, STOPED = @NEW_STOPED, USERDEF1 = @NEW_USERDEF1, USERDEF2 = @NEW_USERDEF2,           USERDEF3 = @NEW_USERDEF3, USERDEF4 = @NEW_USERDEF4, INTERCONTROL = @NEW_INTERCONTROL, USERDEF5 = @NEW_USERDEF5,           USERDEF6 = @NEW_USERDEF6, USERDEF7 = @NEW_USERDEF7, USERDEF8 = @NEW_USERDEF8, USERDEF9 = @NEW_USERDEF9, USERDEF10 = @NEW_USERDEF10,          IORDERNO  = @NEW_IORDERNO ,SEQUENCE = @NEW_SEQUENCE, BOMID = @NEW_BOMID      WHERE E_ORDERD.BILLID = @OLD_BILLID AND E_ORDERD.ITEMNO = @OLD_ITEMNO      FETCH NEXT FROM NEWED INTO @NEW_BILLID, @NEW_ITEMNO, @NEW_GOODSID, @NEW_QTY, @NEW_CANCELED, @NEW_UNITID, @NEW_UNITQTY, @NEW_PRICE,           @NEW_UNITPRICE, @NEW_EXEQTY, @NEW_EXEDRAWQTY, @NEW_AUSERNO, @NEW_STOPED, @NEW_USERDEF1, @NEW_USERDEF2, @NEW_USERDEF3,           @NEW_USERDEF4, @NEW_INTERCONTROL,@NEW_USERDEF5, @NEW_USERDEF6,@NEW_USERDEF7,@NEW_USERDEF8,@NEW_USERDEF9,@NEW_USERDEF10 ,          @NEW_IORDERNO,@NEW_SEQUENCE, @NEW_BOMID      ,@NEW_USERDEF11 
,@NEW_USERDEF12 
,@NEW_USERDEF13 
,@NEW_USERDEF14 
,@NEW_USERDEF15 
,@NEW_USERDEF16 
,@NEW_USERDEF17 
,@NEW_USERDEF18 
,@NEW_USERDEF19 
,@NEW_USERDEF20 
IF (@@FETCH_STATUS = 0)        FETCH NEXT FROM OLDED INTO @OLD_BILLID, @OLD_ITEMNO, @OLD_GOODSID, @OLD_QTY, @OLD_CANCELED, @OLD_UNITID, @OLD_UNITQTY, @OLD_PRICE,             @OLD_UNITPRICE, @OLD_EXEQTY, @OLD_EXEDRAWQTY, @OLD_AUSERNO, @OLD_STOPED, @OLD_USERDEF1, @OLD_USERDEF2, @OLD_USERDEF3,             @OLD_USERDEF4, @OLD_INTERCONTROL,@OLD_SEQUENCE, @OLD_BOMID      ,@OLD_USERDEF11 
,@OLD_USERDEF12 
,@OLD_USERDEF13 
,@OLD_USERDEF14 
,@OLD_USERDEF15 
,@OLD_USERDEF16 
,@OLD_USERDEF17 
,@OLD_USERDEF18 
,@OLD_USERDEF19 
,@OLD_USERDEF20 
IF @@TRANCOUNT > 0        COMMIT TRAN     END      CLOSE NEWED      DEALLOCATE NEWED      CLOSE OLDED      DEALLOCATE OLDED  SET NOCOUNT OFF  
GO