-- =============================================
-- Author:        <Author,yaoyao,Name>
-- Create date: <Create Date,20130515,>
-- Description:    <Description,生成自定义ID,生成规则：1~5个字母前缀加5~10位数字>
-- =============================================
ALTER PROCEDURE [dbo].[proGetCustomID]
    @TableName VARCHAR(50),
    @FieldName VARCHAR(50),
    @BeginChar VARCHAR(5),
    @NumberLen INT=10--默认10
AS
BEGIN
    IF @NumberLen<5 or @NumberLen>10 set @NumberLen=10;
        
    DECLARE @CustomID VARCHAR(10),@MaxID VARCHAR(15),@strSql NVARCHAR(200);
    set @strSql=‘select @tempid = MAX(‘+@FieldName+‘) FROM ‘+@TableName;
    exec sp_executesql @strSql,N‘@tempid VARCHAR(15) out‘,@MaxID out --参数赋值
    --print @MaxID;
    
    IF @MaxID IS NULL
    BEGIN
        SET @CustomID=‘0000000001‘;
    END
    ELSE
    BEGIN
        SET @MaxID = CONVERT(INT,REPLACE(@MaxID,@BeginChar,‘‘));--CONVERT(INT,SUBSTRING(@MaxID,LEN(@BeginChar)+1,10));
        SET @CustomID=@MaxID+1;
        --print @CustomID
        
        WHILE(LEN(@CustomID)<@NumberLen)
        BEGIN
            SET @CustomID=‘0‘+@CustomID
        END
    END
    
    SELECT @BeginChar+@CustomID;
END

/// <summary>
        /// 生成自定义ID
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fieldName">生成id的字段</param>
        /// <param name="beginChar">1~5位字母</param>
        /// <returns></returns>
        public static string CreateID(string tableName, string fieldName, string beginChar)
        {
            SqlParameter[] parameters = {

                    new SqlParameter("@TableName", SqlDbType.VarChar, 20),
                    new SqlParameter("@FieldName", SqlDbType.VarChar, 20),
                    new SqlParameter("@BeginChar", SqlDbType.VarChar,10)};
            parameters[0].Value = tableName;
            parameters[1].Value = fieldName;
            parameters[2].Value = beginChar;
            return DbHelperSQL.GetSingleByPro("proGetCustomID", parameters).ToString();
        }