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的存储过程:)
