sql server 日期+数字生成编号

CREATE  PROCEDURE [dbo].[prGet_NewNo]
@NEW_ID VARCHAR(11) OUTPUT

AS
BEGIN
    DECLARE @CurrDate VARCHAR(8);

    --当前日期 20100619
    SET @CurrDate = CONVERT(VARCHAR(8),GETDATE(),112)
    --取出表中当前日期的已有的最大ID
    SET @NEW_ID = NULL;
    SELECT TOP 1 @NEW_ID = [ID] FROM my_TableWHERE [ID] LIKE @CurrDate+'%' ORDER BY [ID] DESC
    IF @NEW_ID IS NULL
        SET @NEW_ID = (@CurrDate+'001')
    ELSE
    BEGIN
        DECLARE @NUM VARCHAR(8)

        --取出最大的编号加上1
        SET @NUM = CONVERT(VARCHAR, (CONVERT(INT, RIGHT(@NEW_ID, 3)) + 1))
        --因为经过类型转换，丢失了高位的0，需要补上
        SET @NUM = REPLICATE('0', 3 - LEN(@NUM)) + @NUM
        --最后返回日期加编号
        SET @NEW_ID = @CurrDate + @NUM
    END
END
GO