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-'