SQL生成按月的自动编号

CREATE PROCEDURE [dbo].[Select_NO]
(
   @UserId char(12)
 )
AS
SET NOCOUNT ON
declare @maxNO char(6),
@newNo int,
@yearMonth char(6),
@resultNo char(12)

--取当前年月YYYYMM
select @yearMonth = convert(nvarchar(6),getdate(),112) --'YYYYMM’

if not exists(select autoid from Orders where convert(nvarchar(6),OrdDate,112) = @yearMonth and UserID = @UserId)
begin
  select @resultNo = @yearMonth + '000001' 
end
else
begin
--取本月最大值
select @maxNO = substring(MAX(ordNO),7,6) from Orders where convert(nvarchar(6),OrdDate,112) = @yearMonth  and UserID = @UserId
--本月最大值加1
select @newno = convert(int,@maxNO) + 1
select @resultNo = @yearMonth + SUBSTRING('000000',1,6-len(@newno)) + CONVERT(varchar(6),@newno)
end


select @resultNo




if @@error > 0 
  return @@error
else
  return -1