经典的存储过程 （编号：20110619A1515）

USE [db_NLMRPII]GO/****** 对象: StoredProcedure [dbo].[p_Role] 脚本日期: 06/19/2011 15:11:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[p_Role]-- ========================================-- 角色-- ========================================-- 参数: 1、操作类型，2、数据值-- 返回值：0、－100-- 注：5次失败尝试。（锁定失败、死锁）-- ========================================( @strActionType varChar(1000) -- 操作类型: 取值: Insert 、Update 、Delete,@intRoleIds varChar(1000) -- 主键,@intOrganizationId varChar(1000) -- 组织,@strRoleNumber varChar(1000) -- 编号,@strRoleName varChar(1000) -- 名称,@strDescription varChar(1000) -- 描述,@isActive varChar(1000) -- 是否活跃,@strNote varChar(1000) -- 备注,@message varChar(1000) output -- 返回信息,@code varChar(1000) output -- 返回代码)AS-- 定义变量-------------------------------------------------DECLARE @Err INT , -- 错误编号 @ErrCounter INT , -- 错误次数 @ErrCounterMax INT -- 最大错误次数BEGINSET @ErrCounter = 0 SET @ErrCounterMax = 5LockTimeOutRetry: -- 锁定超时入口BEGIN TRY-- （1）Insert-------------------------------------------------IF @strActionType = 'Insert'INSERT INTO t_Role( f_intOrganizationId,f_strRoleNumber,f_strRoleName,f_strDescription,f_isActive,f_strNote)VALUES( @intOrganizationId,@strRoleNumber,@strRoleName,@strDescription,@isActive,@strNote)-- （2）Update-------------------------------------------------IF @strActionType = 'Update'update t_roleset f_intOrganizationId = @intOrganizationId,f_strRoleNumber = @strRoleNumber,f_strRoleName = @strRoleName,f_strDescription = @strDescription,f_isActive = @isActive,f_dtmLastModified = getDate(),f_strNote = @strNotewhere f_intRoleId in ( @intRoleIds )-- （3）Delete-------------------------------------------------IF @strActionType = 'Delete'Delete t_role where [f_intRoleId] in ( @intRoleIds )END TRY-- 错误检测-------------------------------------------------BEGIN CATCHset @Err = @@ERRORIF @Err <> 0 GOTO ErrorHandlerEND CATCH-- Successselect 0 as code, 'Success!' as messageRETURN-- 错误处理-------------------------------------------------ErrorHandler:IF (@err = 1222 OR @err = 1205) AND @ErrCounter >= @ErrCounterMaxBeginRAISERROR('Unable to Lock Data after a few attempts.',16,1)set @code = @errSELECT @message =ERROR_MESSAGE()ENDIF @err = 1222 OR @err = 1205 -- Lock Timeout / DeadlockBEGINWAITFOR DELAY '00:00:00.25'SET @ErrCounter = @ErrCounter + 1GOTO LockTimeOutRetryEND-- else unknown errorset @code = @errSELECT @message = ERROR_MESSAGE() -- RAISERROR( @message ,16,1)select @code as code, @message as messageENDRETURN-- end