SQL事务处理

BEGIN TRANSACTION

DECLARE @errorNum int,@i int,@id varchar(50);
select @errorNum = 0,@i = 1,@id = @orderno;

INSERT INTO dbo.[Order] (ORDER_ID,ORDER_DATE,AGENT_ID,FREIGHT,AMOUNT,SQUARE,PRICE,CONSIGNEE,PHONE,ADDRESS,REMARK,STATUS_ID,CREATER
,CREATE_DATETIME,MODIFY_USER,MODIFY_DATETIME) VALUES (@id,@orderdate,@agent,@freight,@amount,@square,@price,@consignee,@phone,@address,
@remark,'ORDER_WAIT',@creator,GETDATE(),null,null)

select @errorNum = @errorNum + @@error;


DECLARE @opn int;
select @opn = count(*) from OpinionHistory where VALUE_ID = @id and OPINION_ID = 'ORDER_BEGIN';
IF @opn > 0
BEGIN
select @opn = max(seq)+1 from OpinionHistory where VALUE_ID = @id and OPINION_ID = 'ORDER_BEGIN';
END
ELSE
BEGIN
SELECT @opn = 1;
END

INSERT INTO OpinionHistory (VALUE_ID,OPINION_ID,SEQ,OPINION_NAME,OPINION_CLASS,OPINION_RESULT,ADVICE,OPINION_DATETIME,CREATOR,
CREATE_DATETIME) values (@id,'ORDER_BEGIN',@opn,'XXXXX','','1','',GETDATE(),@creator,GETDATE())

select @errorNum = @errorNum + @@error;



create table #tmp(
	ID int identity(1,1) not null,
	LOTID varchar(50) not null,
	WIDTH decimal(10,3) not null,
	LENGTH decimal(10,3) not null
)

exec sp_executesql @ids;

DECLARE @count int;
select @count = count(*) from #tmp;

while @i <= @count
BEGIN
DECLARE @tlotid varchar(50),@twidth decimal(10,3),@tlength decimal(10,3);
select @tlotid = LOTID,@twidth = WIDTH,@tlength = LENGTH from #tmp where ID = @i;
select @twidth = WIDTH,@tlength = LENGTH from #tmp where LOTID = @tlotid;
DECLARE @lotdis varchar(20),@producttype varchar(20),@lotparent varchar(20),@lotmodelid varchar(20),@colorid varchar(20),
				@width decimal(10,3),@length decimal(10,3),@fabric varchar(50),@glue varchar(50),@yarn varchar(50),@packing varchar(50),
				@quality varchar(50),@unit varchar(20),@lottype varchar(20),@num int;
select @lotdis = LOT_DIS,@producttype = PRODUCT_TYPE,@lotparent = LOT_PARENT,@lotmodelid = LOT_MODEL_ID,@colorid = COLOR_ID,
@width = WIDTH,@length = LENGTH,@fabric = FABRIC_TYPE,@glue = GLUE_TYPE,@yarn = YARN_CODE,@packing = PACKING,@quality = QUALITY_ID,
@unit = UNIT,@lottype = LOT_TYPE,@num = UNIT_COUNT from LotInLib where LOTID = @tlotid;
INSERT into OrderLot (ORDER_ID,LOTID,LOT_DIS,PRODUCT_TYPE,LOT_PARENT,LOT_MODEL_ID,COLOR_ID,LENGTH,WIDTH,FABRIC_TYPE,GLUE_TYPE,YARN_CODE,
PACKING,QUALITY_ID,UNIT,LOT_TYPE,UNIT_COUNT) values (@id,@tlotid,@lotdis,@producttype,@lotparent,@lotmodelid,@colorid,@tlength,@twidth,@fabric,@glue,@yarn,
@packing,@quality,@unit,@lottype,@num);
select @errorNum = @errorNum + @@error;

INSERT into LotTie (LOTID,ORDERID,CREATE_DATETIME,WIDTH,LENGTH,CREATOR,TYPE) VALUES (@tlotid,@id,GETDATE(),@twidth,@tlength,@creator,1);
select @errorNum = @errorNum + @@error;

select @i = @i + 1;
END

/*
DECLARE @s varchar(20),@lotdis varchar(20),@producttype varchar(20),@lotparent varchar(20),@lotmodelid varchar(20),@colorid varchar(20),
				@width decimal(10,3),@length decimal(10,3),@fabric varchar(50),@glue varchar(50),@yarn varchar(50),@packing varchar(50),
				@quality varchar(50),@unit varchar(20),@lottype varchar(20),@num int;
select @s = SUBSTRING(@ids,@i,18);
select @lotdis = LOT_DIS,@producttype = PRODUCT_TYPE,@lotparent = LOT_PARENT,@lotmodelid = LOT_MODEL_ID,@colorid = COLOR_ID,
@width = WIDTH,@length = LENGTH,@fabric = FABRIC_TYPE,@glue = GLUE_TYPE,@yarn = YARN_CODE,@packing = PACKING,@quality = QUALITY_ID,
@unit = UNIT,@lottype = LOT_TYPE,@num = NUM from LotInLib where LOTID = @s
INSERT into OrderLot (ORDER_ID,LOTID,LOT_DIS,PRODUCT_TYPE,LOT_PARENT,LOT_MODEL_ID,COLOR_ID,LENGTH,WIDTH,FABRIC_TYPE,GLUE_TYPE,YARN_CODE,
PACKING,QUALITY_ID,UNIT,LOT_TYPE,NUM) values (@id,@s,@lotdis,@producttype,@lotparent,@lotmodelid,@colorid,@length,@width,@fabric,@glue,@yarn,
@packing,@quality,@unit,@lottype,@num);
select @errorNum = @errorNum + @@error;*/

IF @errorNum > 0
BEGIN
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
COMMIT TRANSACTION;
END

select @return_id = @id;