test

create  proc testNumber
(
 @ym varchar(8)
)
AS
BEGIN
  SELECT @ym + RIGHT(100001 + ISNULL(RIGHT(MAX(Number), 5), 0), 5)
  FROM testTable WITH(XLOCK,PAGLOCK)
  WHERE Number like @ym+'%'
END

exec testNumber '2007-9-'