字符生成下一个编码

IF OBJECT_ID('NEXTCODE') IS NOT NULL DROP PROCEDURE NEXTCODE
GO

/*
自动生成所设置的字符串的下一个编码，只计算数字 部分，如果没有数字时，按设定的升序在编码尾部生成第一个字符：

如：空串  > 01
ABC01 > ABC02
A9BC9  > A10BC0
ABC99  > ABC110
'' > 01  默认长度为2 。

AUTH:LYPingfan
*/

CREATE PROCEDURE [dbo].[NEXTCODE] @sStr VARCHAR(100),@nextStr VARCHAR(100) OUT ,@len INT =2
AS
BEGIN
	DECLARE
		 @s VARCHAR(1);
	DECLARE
		@m INT, @n INT, @j INT,@FistNum INT

	--SET @len = 3

	SET @j = 0;
	--SET @sStr = '';
	SET @NextStr = '';
	SET @m = len(@sStr);
	SET @n = 0;
	SET @FistNum = 1
	WHILE @m > @n
	BEGIN
		SET @s = substring(@sStr, @m, 1);

		IF @s >= '0'
		   AND  @s <= '9'
		BEGIN
			IF @FistNum =1 --未位开始 + 1 
			BEGIN
			 SET @FistNum =0 
				IF @s = '9'
				BEGIN
					SET @NextStr = '0' + @NextStr;
					SET @j = 1;  
				END
				ELSE
				BEGIN
					SET @NextStr = cast(@s  +1 AS VARCHAR(1) )+ @NextStr;
					SET @j = 0;
				END
			END
			ELSE BEGIN
				IF @s = '9' AND @j= 1
				BEGIN
					SET @NextStr ='0'+ @NextStr;
					SET @j = 1;								 
				END
				ELSE
				begin
					SET @NextStr = cast(@s  +@j AS VARCHAR(1) )+ @NextStr;
					SET @j = 0;
				END;
			END;

		   -- SET @NextStr = @s + @NextStr;
		END;
		ELSE BEGIN
			SET @NextStr = @s + @NextStr;
		END;

	   -- SELECT  @sStr, @m, @s, @NextStr;

		SET @m = @m - 1;
	END;
	--SELECT @j AS 最后的进位标志
	IF @j = 1 --最高进十
	BEGIN
	  SET @n= PATINDEX('%[0-9]%', @NextStr);
	  SET @NextStr = substring(@NextStr,0,@n)+ '1' +  substring(@NextStr,@n,len(@NextStr))
	END
	else
	IF @sStr= @NextStr --无数字时 
	BEGIN
	  SET @NextStr = @NextStr +replace(str(1,@len),' ','0')
	END

	--SELECT @sStr
	--SELECT @NextStr
END;