-- 得到新编号的函数
CREATE FUNCTION TnextBH()
RETURNS char ( 8 )
AS
BEGIN
RETURN
(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM TestWITH(XLOCK,PAGLOCK))
END
GO
--在表中应用函数
CREATE TABLE Test(
BH char(8) PRIMARY KEY DEFAULT dbo.TnextBH(),
col int)
--插入资料
BEGIN TRAN
INSERT Test(col) VALUES(1)
INSERT Test(col) VALUES(2)
INSERT Test(col) VALUES(3)
DELETE TestWHERE col=3
INSERT Test(col) VALUES(4)
INSERT Test(BH,col) VALUES(dbo.TnextBH(),14)
COMMIT TRAN
--显示结果
SELECT * FROM Test
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/
//前面带0001的
SELECT NUM = RIGHT('00'+RTRIM(ISNULL(MAX(CAST(SUBSTRING(ProjectNum,6,5) as int)),0)+1),4) FROM
TestNum WHERE SUBSTRING(ProjectNum,0,5)='2011'