SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --获取销售订单编号 ALTER PROCEDURE [dbo].[DCM_GenerateSaleOrderNo] AS DECLARE @ErrorCode nvarchar(10) DECLARE @ErrorMsg nvarchar(255) DECLARE @OrderNo nvarchar(50) DECLARE @OldOrderNo nvarchar(50) DECLARE @MaxSeqNo int DECLARE @MaxSeqString nvarchar(10) DECLARE @iCount int BEGIN --SO20100427000 --销售订单号为SO + YYYYMMDD + (三位最大流水号 + 1) SET @iCount = 0 SET @ErrorCode = '' SET @ErrorMsg = '' SET @OldOrderNo = '' SET @MaxSeqNo = 0 SET @OrderNo = 'SO' + convert(nvarchar(10),getdate(),112) BEGIN TRY BEGIN TRANSACTION SELECT @OldOrderNo =CodingRoleString FROM TBL_CODINGROLESTRINGTEMP WITH (UPDLOCK) WHERE TABLENAME='DCM_ORDER' -- WHILE @iCount<1000000 -- BEGIN -- PRINT @iCount -- SET @iCount = @iCount + 1 -- END IF @OldOrderNo<>'' BEGIN IF @OrderNo = substring(@OldOrderNo,1,10) BEGIN SET @MaxSeqNo = cast(substring(@OldOrderNo,11,4) as int) SET @MaxSeqNo = @MaxSeqNo + 1 END ELSE SET @MaxSeqNo = 0 IF @MaxSeqNo>9999 BEGIN SET @ErrorCode = 'E0001' SET @ErrorMsg='订单流水号超出最大限制9999!' RAISERROR(@ErrorMsg,1,16) END ELSE BEGIN SELECT @MaxSeqString = right('0000' + cast(@MaxSeqNo as nvarchar(10)),4) SET @OrderNo = @OrderNo + @MaxSeqString UPDATE TBL_CODINGROLESTRINGTEMP SET CodingRoleString=@OrderNo WHERE TABLENAME='DCM_ORDER' END END ELSE BEGIN SET @ErrorCode = 'E0002' SET @ErrorMsg='临时表不存在DCM_ORDER的订单编号数据,请检查!' RAISERROR(@ErrorMsg,1,16) END SELECT @OrderNo AS OrderNo COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION IF @ErrorCode='' SET @ErrorMsg='意外出错!' RAISERROR(@ErrorMsg,1,16) END CATCH END GO
SQL 存储过程示例02
最新推荐文章于 2024-10-12 22:28:01 发布