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!