2021-03-18存储过程加事务处理防止超出数量

--存储过程加事务处理防止超出数量

USE [LQSCAN]

GO

/**** Object: StoredProcedure [dbo].[UpScanBarCode] Script Date: 2021/3/18 14:02:32 ****/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[UpScanBarCode]

-- Add the parameters for the stored procedure here

@OrderNO varchar(9),

@PartCode nvarchar(50),

@BarCode varchar(13),

@Note nvarchar(500)


AS

DECLARE @ERROR INT = -1 --事务中操作记录 0:条码存在; 1：订单与物料不存在; 2：数量超出需求数量;13:条码长度不对;200：数据提交成功;

 ,@ISEXIST INT = 0

 ,@ISOK INT = 0

 ,@BARCODEISEXIST INT = 0


BEGIN

BEGIN TRY

		--开启事务

		BEGIN TRAN

		--判断条码是否是13位

		IF LEN(@BarCode) <>13

			BEGIN

			  SET @ERROR = 13

			  RAISERROR('PARAM ERRPR0',16,1)

			END

		--判断条码是否已存在

		SELECT @BARCODEISEXIST = COUNT(1) FROM [LQSCAN].[dbo].[ScanLog] WHERE [BarCode] = @BarCode

		IF(@BARCODEISEXIST > 0)

			BEGIN

			  SET @ERROR = 0

			  RAISERROR('PARAM ERRPR0',16,1)

			END

		--添加扫描数据

		INSERT INTO  [LQSCAN].[dbo].[ScanLog]([BarCode],[OrderNO],[Note])VALUES (@BarCode,@OrderNO,@Note)

		--更新对应数量

		UPDATE [LQSCAN].[dbo].[OrderInfo] SET [ScanQty] = [ScanQty] +1  WHERE [OrderNO] = @OrderNO AND [PartCode] = @PartCode 

	

	    --判断总装订单与物料编码是否存在

	    SELECT @ISEXIST  = COUNT(1)  FROM [LQSCAN].[dbo].[OrderInfo]  WHERE [OrderNO] = @OrderNO AND [PartCode] = @PartCode

	

		IF( @ISEXIST < 1 )

			BEGIN

			  SET @ERROR = 1

			  RAISERROR('PARAM ERRPR1',16,1)

			END

		--判断数量是否匹配完成

		SELECT @ISOK = COUNT(1) FROM [LQSCAN].[DBO].[ORDERINFO] WHERE  [OrderNO] = @OrderNO AND [PartCode] = @PartCode AND [ScanQty] > [NeedsQty]

		IF( @ISOK > 0)

			 BEGIN

			  SET @ERROR = 2

			  RAISERROR('PARAM ERRPR2',16,1)

			END

		SET @ERROR = 200

		COMMIT TRAN

		

END TRY

BEGIN CATCH

	ROLLBACK TRAN

END CATCH

RETURN @ERROR


END