SQL 存储过程示例02

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