Use Try Catch handling exception _mssql2005 in SQL Server 2005

TRY ... Catch is an impressive new feature of SQL Server 2005/2008. Improved developer exception handling. There is no reason not to try. Catch function.

* TRY block-Contains code or script that may produce an exception
* Catch Block-If an exception occurs in the try block, the code processing flow is routed to the catch block. Here you can handle exceptions, log logs, etc.
Language such as Try catch and C#,java in SQL Server is handled in a consistent way. This consistency is the greatest innovation.

exception handling in SQL SERVER 2000

 CREATE PROC usp_accounttransaction @AccountNum INT, @Amount DECIMAL as is 
 
    GIN BEGIN TRANSACTION--beginning a TRANSACTION. UPDATE mychecking SET Amount = amount-@Amount WHERE accountnum = @AccountNum IF @ @ERROR!= 0--check @@e 
 
    Rror variable after each DML statements. 
 
      BEGIN ROLLBACK TRANSACTION--rollback TRANSACTION if Error. Return end ELSE BEGIN UPDATE mysavings SET Amount = Amount + @Amount WHERE Accoun 
 
      Tnum = @AccountNum IF @ @ERROR!= 0--check @ @ERROR variable after each DML statements. 
 
        BEGIN ROLLBACK TRANSACTION--rollback TRANSACTION if Error. Return end ELSE BEGIN commit TRANSACTION--finally, commit the TRANSACTION if succes S.. return-end-end-Go 

Above is a stored procedure in SQL Server 2000 that must be checked immediately after each database operation to commit/rollback the transaction.
monitoring errors in SQL Server 2000 can only traverse the @ @ERROR by monitoring the global. Because the @ @ERROR is overwritten by the next database operation. Therefore, it must be monitored immediately after each operation is completed.

Ii. exception Handling in SQL SERVER 2005

TRY ... Catch is the more readable syntax provided by SQL Server 2005. Each developer is familiar with this notation. SQL Server 2005 still supports the use of @ @ERROR.

1.try catch Syntax:

BEGIN try 
 
  try Statement 1 
 
  try Statement 2 
 
  ... 
 
  Try Statement M end 
 
TRY 
 
BEGIN catch 
 
  catch Statement 1 
 
  catch Statement 2 
 
  ... 
 
  Catch Statement N 
 

2. function table to get error message:

The following system functions are valid in the CATCH block. Can be used to get more error messages:

Function description

Error_number () returns the error number of the error message that caused the CATCH block to run.
Error_severity () returns the severity level of the error message that caused the CATCH block to run
Error_state () returns the status number of the error message that caused the CATCH block to run
Error_procedure () returns the name of the stored procedure in which the error occurred
Error_line () returns the line number where the error occurred
Error_message () returns the full text of the error message that caused the CATCH block to run

Simple example:

BEGIN TRY 
 
  Select GETDATE () 
 
  select 1/0--evergreen divide by zero example! 
 
End TRY 
 
BEGIN CATCH 
 
  SELECT ' There is an error! ' + error_message () return-end 
 
CATCH; 

Example of a 3.try catch rollback/COMMIT TRANSACTION

ALTER PROC usp_accounttransaction 
 
  @AccountNum INT, 
 
  @Amount DECIMAL 
 
as 
 
begin 
 
  begin TRY--start the Try block ... 
 
    BEGIN TRANSACTION-Start the TRANSACTION. 
 
      Update mychecking Set Amount = amount-@Amount 
 
        WHERE accountnum = @AccountNum 
 
      UPDATE mysavings SET Amount = Amoun T + @Amount 
 
        WHERE AccountNum = @AccountNum 
 
    COMMIT TRAN--Transaction success! 
 
  End TRY 
 
  BEGIN CATCH 
 
    IF @ @TRANCOUNT > 0 
 
      ROLLBACK TRAN--rollback into case of Error 
 
    --Can Raise E Rror with RaiseError () Statement including 
 
    the details of the exception RAISERROR (Error_message (), error_severity () , 1) End CATCH end Go 
 

Third, the example explanation

To create an error log table:

CREATE TABLE ErrorLog (errnum Int,errsev NVARCHAR (1000), errstate Int,errproc (NVARCHAR), 1000 INT, Errline errmsg (2000))

To create an error log record stored procedure:

CREATE PROCEDURE ErrorLog as SELECT error_number () as errnum,error_severity () as 
   errsev,error_state () as Errstate,error_procedure () as Errproc,error_line () as Errline,error_message () as ErrMsg 
   INSERT into 
   ErrorLog VALUES (Error_number (), error_severity (), Error_state (), 
   error_procedure (), Error_line (), Error_message ())
Go

Write a stored procedure! Inside use try Catch:

Use [your_test]
go
/****** object:storedprocedure [dbo].[ Gettodaybirthday]  
    Script date:05/17/2010 15:38:46 
    author:jinho
    Desc: Get everyone on that day's birthday
    ******/
SET ANSI_NULLS on
go
SET quoted_identifier on
go
ALTER PROCEDURE [dbo].[ Gettodaybirthday]
as
BEGIN TRY
 declare @today datetime; 
 SET @today = GETDATE ();--Get today's date
 DECLARE @day VARCHAR (2);
 SET @day =replace (Day (@today), 0, "");
 DECLARE @month VARCHAR (2);
 SET @month = REPLACE (Month (@today), 0, "");
 DECLARE @year VARCHAR (4);
 SET @year = year (@today);
 SELECT * FROM dbo. UserInfo WHERE REPLACE (Day (CONVERT (datetime,birthday)), 0, ') = @day and REPLACE (MONTH (CONVERT (datetime,brithday)), 0, ') = @month and birthday is not NULL end 

 TRY
 BEGIN catch
 ErrorLog-invoke the above stored procedure to save the error log end
 CATCH

Description: Error_number (), error_severity (), Error_state (), Error_procedure (), Error_line (), Error_message () These functions can only be used in catch!