USE [db_NLSOS]
GO
/****** 对象: StoredProcedure [dbo].[p_get_new_number] 脚本日期: 03/12/2012 11:27:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_get_new_number]
-- ========================================
-- 获取新的编号
-- ========================================
-- 参数: 编号的类型
-- 返回值:
-- 注:5次失败尝试。(锁定失败、死锁)
-- ========================================
-- 测试数据
-- (需补充)
-- ========================================
(
-- 参数
@number_category varChar(1000) = '' -- 编号类别
)
AS
-- 定义变量
-------------------------------------------------
DECLARE
@Err INT -- 错误编号
,@ErrCounter INT -- 错误次数
,@ErrCounterMax INT -- 最大错误次数
,@code varChar(1000) -- 返回代码
,@message varChar(1000) -- 返回信息
-- 声明变量
declare @new_number varchar(50)
set @code = '0' -- 默认值
set @message = 'success' --
set @new_number = '0' --
-- 存储过程 Start
BEGIN
-- 事务 Start
BEGIN TRANSACTION TRANS
SET @ErrCounter = 0
SET @ErrCounterMax = 5
LockTimeOutRetry: -- 锁定超时入口
BEGIN TRY
-------------------------------------------------
-- 1、更新数值
-- 2、取得编号
-------------------------------------------------
-- 1、更新数值
update t_number
set [current_number] = [current_number] + 1
where category = @number_category
-- 2、取得编号
select
@new_number
= [prefix]
+ REPLICATE('0',len([format])- len([current_number]))
+ cast([current_number] as varchar)
+ [suffix]
from t_number
where category = @number_category
END TRY
-- 错误检测
-------------------------------------------------
BEGIN CATCH
set @Err = @@ERROR
IF @Err <> 0
BEGIN
ROLLBACK TRANSACTION TRANS
GOTO ErrorHandler
END
END CATCH
COMMIT TRANSACTION TRANS
-- Success
select
@code as [code]
,@message as [message]
,@new_number as [new_number]
RETURN
-- 错误处理
-------------------------------------------------
ErrorHandler:
IF (@err = 1222 OR @err = 1205) AND @ErrCounter >= @ErrCounterMax
Begin
RAISERROR('Unable to Lock Data after a few attempts.',16,1)
set @code = @err
SELECT @message =ERROR_MESSAGE()
END
IF @err = 1222 OR @err = 1205 -- Lock Timeout / Deadlock
BEGIN
WAITFOR DELAY '00:00:00.25'
SET @ErrCounter = @ErrCounter + 1
GOTO LockTimeOutRetry
END
-- else unknown error
set @code = @err
SELECT @message = ERROR_MESSAGE()
-- RAISERROR( @message ,16,1)
select
@code as [code]
,@message as [message]
END
RETURN
-- end
存储过程:p_get_new_number
最新推荐文章于 2024-08-12 18:28:34 发布