SQL Server的Exception Handling

   [ , [ @lang = ] 'language' ]
   [ , [ @with_log = ] 'with_log' ]
   [ , [ @replace = ] 'replace' ]
  
  sp_dropmessage [ @msgnum = ] message_number [ , [ @lang = ] 'language' ]
  sp_altermessage [ @message_id = ] message_number , [ @parameter = ] 'write_to_log' , [ @parameter_value = ] 'value'
  关于如何使用这些stored procedure，可以参阅SQL Server Books Online。在这里，我同下面的script添加我需要的Error。
  
  
  sp_addmessage @msgnum = 50001,@severity = 16,@msgtext = N'This user is already existent' , @replace = 'replace'
  GO
  sp_addmessage @msgnum = 50002,@severity = 16,@msgtext = N'This role is already existent', @replace = 'replace'
  Go
  sp_addmessage @msgnum = 50003,@severity = 16,@msgtext = N'This user does not exist', @replace = 'replace'
  GO
  sp_addmessage @msgnum = 50004,@severity = 16,@msgtext = N'This role does not exist', @replace = 'replace'
  GO
  sp_addmessage @msgnum = 50005,@severity = 16,@msgtext = N'This user is already in the role', @replace = 'replace'
  GO
  
  [注：直接操作sys.messages是不被允许的]
  
  五、 ADO.NET Exception Handling
  上面所有的都在介绍在Database层面如何进行Exception handling，下面我们同一个简单的Demo，简单介绍一个我么的.NET Application如何处理从Database Engine抛出的Exception。在这里我们使用一个简单的Cosole application模拟一个简单的Security方面的场景：创建用户、创建角色、添加用户到角色。大部分的功能都在上面提到了，在这里做一下总结：
  
  2． Messages（通过上面一节末Script在sys.messages中创建）：
  · 50001：This user is already existent
  · 50002：This role is already existent
  · 50003：This user does not exist
  · 50004：This role does not exist
  · 50005：This user is already in the role
  
  3． Stored procedure
  · Create User：P_USERS_I
  
  
  CREATE Procedure P_USERS_I
   (
   @user_id varchar(50),
   @user_name nvarchar(256)
   )
  AS
  
  DECLARE @error_number INT
  DECLARE @error_serverity INT
  DECLARE @error_state INT
  
  BEGIN TRY
  
  IF(EXISTS(SELECT * FROM dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name) OR [USER_ID] = @user_id))
   BEGIN
   RAISERROR (50001,16,1)
   END
  
  INSERT INTO dbo.T_USERS
   ([USER_ID]
   ,[USER_NAME]
   ,LOWERED_USER_NAME)
   VALUES(@user_id, @user_name, LOWER(@user_name))
  
  END TRY
  
  BEGIN CATCH
  
   SET @error_number = ERROR_NUMBER()
   SET @error_serverity =ERROR_SEVERITY()
   SET @error_state = ERROR_STATE()
   RAISERROR(@error_number,@error_serverity,@error_state)
  
  END CATCH
  
   · Create Role：T_ROLES_I
  
  CREATE Procedure T_ROLES_I
   (
   @role_id varchar(50),
   @role_name nvarchar(256)
   )
  AS
  
  DECLARE @error_number INT
  DECLARE @error_serverity INT
  DECLARE @error_state INT
  
  BEGIN TRY
  
  IF(EXISTS(SELECT * FROM dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@ROLE_name) OR [ROLE_ID] = @role_id))
   BEGIN
   RAISERROR (50002,16,1)
   END
  
  INSERT INTO dbo.T_ROLES
   ([ROLE_ID]
   ,[ROLE_NAME]
   ,LOWERED_ROLE_NAME)
   VALUES(@ROLE_id, @ROLE_name, LOWER(@ROLE_name))
  
   END TRY
  
  BEGIN CATCH
  
   SET @error_number = ERROR_NUMBER()
   SET @error_serverity =ERROR_SEVERITY()
   SET @error_state = ERROR_STATE()
   RAISERROR(@error_number,@error_serverity,@error_state)
  END CATCH
  
  · Add User in Role：P_USERS_IN_ROLES_I
  
  CREATE Procedure P_USERS_IN_ROLES_I

（编辑：IT资讯之家 www.it55.com）