用存储过程给固定位数字串自动增1,在不足位前补0

CREATE PROCEDURE dbo.my_addProd
    @brdName varchar(30),
    @prodName varchar(30),
    @prodScope varchar(30),
    @prodUnit char(10),
    @mugong  Decimal(19,2),
    @diqi  Decimal(19,2),
    @damou  Decimal(19,2),
    @mianqi  Decimal(19,2),
    @zhuangdian  Decimal(19,2),
    @anzhuang  Decimal(19,2),
    @another  Decimal(19,2)
 AS
DECLARE @alias char(3)
DECLARE @prodAlias char(6) //自动增1数字串变量
DECLARE @t int
   SET @alias =(SELECT BrdAlias FROM BrdTB WHERE BrdName =@brdName)
   //获得增1数字串
   SET @prodAlias=(SELECT 
      CASE 
          WHEN ISNULL(MAX(ProdAlias) ,0)=0 THEN '000001'
          WHEN MAX(ProdAlias)<9 THEN '00000'+LTRIM(STR(MAX(ProdAlias)+1))
          WHEN MAX(ProdAlias) BETWEEN 9 AND 98 THEN '0000'+LTRIM(STR(MAX(ProdAlias)+1))
          WHEN MAX(ProdAlias) BETWEEN 99 AND 998 THEN '000'+LTRIM(STR(MAX(ProdAlias)+1))
          WHEN MAX(ProdAlias) BETWEEN 999 AND 9998 THEN '00'+LTRIM(STR(MAX(ProdAlias)+1))
          WHEN MAX(ProdAlias) BETWEEN 9999 AND 99998 THEN '0'+LTRIM(STR(MAX(ProdAlias)+1))
          WHEN MAX(ProdAlias) BETWEEN 99999 AND 999998 THEN LTRIM(STR(MAX(ProdAlias)+1))
          ELSE 'F'
      END AS ProdAlias FROM ProdTB where BrdAlias=@alias)

IF @prodAlias='F' //如果溢出
    BEGIN
     SET @t=0
    END
ELSE   
    BEGIN
        IF EXISTS(SELECT *FROM ProdTB WHERE ProdName=@prodName AND ProdScope=@ProdScope and BrdAlias=@alias)
           BEGIN
             SET @t=1
           END
        ELSE
         BEGIN
            INSERT INTO ProdTB values(@alias,@prodAlias,@prodName,@prodScope,@prodUnit,@mugong,@diqi,@damou,@mianqi,@zhuangdian,@anzhuang,@another)
           SET @t=2
         END
    END
SELECT @t
GO
      这是我在做人事薪资管理系统中,做的一个给固定位数字串自动增1的存储过程:)

转载于:https://www.cnblogs.com/172838427/archive/2007/01/10/616464.html