USE [db_NLMRPII] GO /****** 对象: StoredProcedure [dbo].[p_Role] 脚本日期: 06/19/2011 15:11:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER 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 -- 最大错误次数 BEGIN SET @ErrCounter = 0 SET @ErrCounterMax = 5 LockTimeOutRetry: -- 锁定超时入口 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_role set f_intOrganizationId = @intOrganizationId ,f_strRoleNumber = @strRoleNumber ,f_strRoleName = @strRoleName ,f_strDescription = @strDescription ,f_isActive = @isActive ,f_dtmLastModified = getDate() ,f_strNote = @strNote where f_intRoleId in ( @intRoleIds ) -- (3)Delete ------------------------------------------------- IF @strActionType = 'Delete' Delete t_role where [f_intRoleId] in ( @intRoleIds ) END TRY -- 错误检测 ------------------------------------------------- BEGIN CATCH set @Err = @@ERROR IF @Err <> 0 GOTO ErrorHandler END CATCH -- Success select 0 as code, 'Success!' as message 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