CREATE PROCEDURE MakeMedicineNo
@ShipId varchar(30),
@ProductId varchar(30),
@AreaNo varchar(5),
@Quantity int
AS
BEGIN TRAN MakeMedicineNo
DECLARE @maxID INT,@maxSerial varchar(6) ,@i int,@CardNo varchar(20),@IceNo varchar(20),@MedicineNo varchar(24),@Six varchar(6)
SET @i=0
SELECT @maxID=MAX(Serial) FROM WinShipNo WHERE [email protected]
IF @maxID IS NULL
BEGIN
SET @maxID=0
END
CREATE TABLE #(Serial int, SerialNo varchar(30))
WHILE @i <@Quantity
BEGIN
--SELECT @maxSerial=REPLICATE( '0',6-LEN(@maxID)) + cast(@maxID as varchar),@[email protected]+1
SET @[email protected]+1
INSERT #(Serial,SerialNo) SELECT @maxID, REPLICATE( '0',6-LEN(@maxID))+CAST(@maxID AS varchar)
SET @i [email protected] +1
END
SET @MedicineNo=REPLICATE( '&',12-LEN(@ProductId))[email protected]
SET @Six=+'000000'
SET @[email protected]+'0000000000001'
SET @[email protected]+'0000000008888'
INSERT WinShipNo(ShipId,IceNo,ProductId,MedicineNo,Serial,InTime,CardNo,[Sign])
SELECT @ShipId,@IceNo,@ProductId,@[email protected],Serial,GETDATE(),@CardNo,0
FROM #
/*
SELECT @maxID=MAX(FlowNum) FROM WinShipFlowIfUser WHERE ProductId='A' AND IfUse=0
SELECT @maxSerial=REPLICATE( '0',6-LEN(RTRIM(@maxID))) + cast(@maxID as varchar)
SET @[email protected]+1
INSERT WinShipFlow (ProductId,FlowNo) SELECT 'A',@maxSerial
INSERT WinShipFlowIfUser (ProductId,FlowNum) SELECT 'A',@maxID
UPDATE WinShipFlowIfUser set IfUse=1 where ProductId='A' and FlowNum=(@maxid-1)
*/
COMMIT TRAN MakeMedicineNo--事务提交
IF (@@error <> 0) -- 事务回滚
BEGIN
ROLLBACK TRANSACTION MakeMedicineNo
RETURN 0
END
RETURN 1
GO
------解决方案--------------------
可能会出问题，2次INSERT，第2次@@ERROR值已经改变了，可以用变量保存下，最后判断2个值，如果都正确，才提交，否则回滚
------解决方案--------------------
你可以在上面出错的地方就回滚事务，然后就退出存储过程。不需要在最后再来判断这个。