set nocount on
go
drop table test
go
create table test(no varchar(20))
go
insert into test(no)
select 'BH' + right('000' + cast((substring(isnull(max(no),'BH000'),3,3) + 1) as varchar(20)),3)
from test
go 10
select * from test
go开始执行循环
批处理执行已完成 10 次。
no
--------------------
BH001
BH002
BH003
BH004
BH005
BH006
BH007
BH008
BH009
BH010
;WITH TB(BH) AS(
SELECT 'BH001'
UNION ALL SELECT 'BH002'
UNION ALL SELECT 'BH003'
)
--以上模拟数据
--以下开始查询
SELECT 'BH'+RIGHT('00'+LTRIM(RIGHT(BH,3)+1),3)BH
FROM(SELECT MAX(BH)BH FROM TB)T
select 'BH001' union all
select 'BH002' union all
select 'BH003'
)
select 'BH'+right('000'+ltrim(substring(MAX(id),3,3)+1),3) as id from a
DROP TABLE TB
GO
;WITH [CTE](BH)AS(
SELECT'BH001'
UNION ALL SELECT'BH002'
UNION ALL SELECT'BH003'
)
SELECT * INTO TB FROM CTEBEGIN TRAN Tran1 --开始事务DECLARE @tran_error int;
SET @tran_error = 0;
BEGIN TRY
DECLARE @BH VARCHAR(5)
--查找下一个编号
SELECT @BH=('BH'+RIGHT('00'+LTRIM(RIGHT(BH,3)+1),3))
FROM(SELECT MAX(BH)BH FROM TB)T
--因为我这边没有其它的逻辑,我就直接新增编号了
INSERT INTO TB(BH)VALUES(@BH)
END TRYBEGIN CATCH
PRINT '出现异常,错误编号:' + convert(varchar,error_number()) + ',错误消息:' + error_message()
SET @tran_error = @tran_error + 1
END CATCHIF(@tran_error > 0)
BEGIN
--执行出错,回滚事务
ROLLBACK TRAN;
PRINT '执行出错!';
END
ELSE
BEGIN
--没有异常,提交事务
COMMIT TRAN;
PRINT '操作成功!';
END
SELECT * FROM TB