SQL之字符串字段也可自增

 -- =============================================
-- 创建人   ：郑紫至
-- 创建日期 : 2009-9-11
-- 参数描述 : @TableName 自增字串所在的表名称，@AutoIdStart
--            自增自段ID前几位字符，@AutoIdName自增字段的名称
-- 功能说明 ：创建指定表内字串ID自增主键的值
-- 测试方法 ：exec [dbo].[sp_CreateAutoIdString]('CapacitorPrice','C-','PriceNo')
--            显示结果为“C-000001” 、“C-000002”   等等格式    
-- =============================================
CREATE PROCEDURE  [dbo].[sp_CreateAutoIdString]
(
@TableName VARCHAR(200),
@AutoIdStart VARCHAR(3),
@AutoIdName VARCHAR(50)
)

AS
 
BEGIN
 
 DECLARE  @AutoId VARCHAR(50)
         SET @AutoId=''
         DECLARE  @RecordCount INT
         DECLARE @countSql nvarchar(4000) 
      SET @countSql='SELECT @RecordCount=Count(*) From '+@TableName
      execute sp_executesql @countSql,N'@RecordCount int out',@RecordCount out
       
      IF @RecordCount=0
         BEGIN
                       SET @AutoId= @AutoIdStart+'000001'
         END

      ELSE

         BEGIN
        DECLARE @MaxInt INT
        SET @AutoId =  @AutoIdStart
        DECLARE @MaxIntSql nvarchar(4000) 
        SET @MaxIntSql='SELECT @MaxInt=MAX(CAST(SUBSTRING('+@AutoIdName+',4,6) AS INT))+1 FROM  '+@TableName
        execute sp_executesql @MaxIntSql,N'@MaxInt int out',@MaxInt out
       SET @MaxInt= @MaxInt
                          
         
        IF LEN(@MaxInt)=1
         BEGIN
                        SET @AutoId =@AutoIdStart + '00000'+CAST(@MaxInt  AS VARCHAR(1))
         END
       
       IF LEN(@MaxInt)=2
         BEGIN
                         SET @AutoIdStart =@AutoIdStart + '0000'+CAST(@MaxInt  AS VARCHAR(2))
         END
        
       IF LEN(@MaxInt)=3
         BEGIN
                        SET @AutoId =@AutoIdStart +'000'+ CAST(@MaxInt  AS VARCHAR(3))
         END
         
          IF LEN(@MaxInt)=4
         BEGIN
                        SET @AutoId =@AutoIdStart+'00' + CAST(@MaxInt  AS VARCHAR(4))
         END
        IF LEN(@MaxInt)=5
         BEGIN
                        SET @AutoId =@AutoIdStart+'0' + CAST(@MaxInt  AS VARCHAR(5))
         END
        IF LEN(@MaxInt)=5
         BEGIN
                        SET @AutoId =@AutoIdStart + CAST(@MaxInt  AS VARCHAR(6))
         END

        END

       SELECT @AutoId
END