Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This Blog is About SQL Server Technolgy
Browse by Tags · View All
BRH 12
#WHITEPAPERS 10
whitepapers 10
webcast 10
Training 9
MSBI 9
BI 8
SQL-Server 8
SSAS 7
T-SQL 7

Archive · View All
August 2010 9
July 2010 7
May 2009 6
September 2010 4
June 2009 4
July 2009 3
October 2010 2
April 2010 2
March 2010 1

Ashish's Blog On SQL Server

Error/Exception Handling in SQL Server 2005

May 9 2009 11:16AM by Ashish Gilhotra   

Being a .Net developer, the most useful feature i found was the Error Handling or Exception Handling technique.When it comes to programming languages , all popular OOP languages have TRY()…Catch() method to handle an exception.

In Sql Server 2005, error handling was made easy . This article demonstrates various methods of handling errors using
Try()..Catch() in T-SQL.

Here you try something like that…

declare @i tinyint                     
select @i=count(*) from sys.sysobjects  
select @i*999999999 as myval

When you try to run this you will get a error like this…

-- Error

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 1812.

(1 row(s) affected)

 

Now try to run it something like this….

 

begin try                              
    declare @i tinyint                     
    select @i=count(*) from sys.sysobjects 
    select @i*999999999 as myval           
end try                                
begin catch                            
    select                                 
    error_line() as errornumber    
end catch    

 

you will get result like

 

errornumber
------------------
4

 

so now this time it throws the number of error.

 

You can also get more parameters  like Error_Line from this like,,,

begin try                              
    declare @i tinyint                     
    select @i=count(*) from sys.sysobjects 
    select @i*999999999 as myval           
end try                                
begin catch                            
    select                                 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
 
end catch  

 

error_handling1

 

like this……

 

Now you can also make a Store Procedure like this which throws error and use this in your try()..catch() statement like this…

CREATE PROCEDURE Sp_ThrowErrorInfo
AS
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
GO

 

then try  like this..

BEGIN TRY
    declare @i tinyint                     
    select @i=count(*) from sys.sysobjects 
    select @i*999999999 as myval 
END TRY
BEGIN CATCH
   Exec Sp_ThrowErrorInfo
END CATCH;

This gives you same results as above.

 

You can also use it with a procedure like if you have some kind of procedure than you want to associate with it
Try()…Catch() block then do something like that

 

First create a Procedure which contains SQL statements to do a specific task, i made a Store procedure which select all the data from a table which doesn’t present in the database 

CREATE PROCEDURE Sp_ErrorTest
AS
    SELECT * FROM NonexistentTable;
GO

then do like this….

 

BEGIN TRY
    EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
     EXECUTE Sp_ThrowErrorInfo
END CATCH;

This will show you error,, so in this way you can use Store Procedures in Error Handling

 

There are many kind of errors which you able to trace with Try()…Catch() statements in T-SQL

The following system functions can be used to obtain information about the error that CATCH block  to be executed:

 

  • ERROR_NUMBER() returns the number of the error.
  • ERROR_SEVERITY() returns the severity.
  • ERROR_STATE() returns the error state number.
  • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE() returns the line number inside the routine that caused the error.
  • ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

 

If these functions are used outside the CATCH block, then all of them will return NULL value.

 

You can also use Try()..Catch() statement  to handle errors that occur inside a transaction, by checking the value of  XACT_STATE . The XACT_STATE function determines whether the transaction should be committed or rolled back.

You can use XACT_STATE_ABORT is on, this makes transaction uncommittable when error occurs..

 

If XACT_STATE =1 , transaction is committable.

If XACT_STATE=-1, the transaction is uncommittable and should be rolled back.

If XACT_STATE=0, means that there is no transaction and a commit or rollback operation would generate an error.

 

 

In this way there is a lot more to do in error handling, and there are commands which give you very fine-grained control over small set of errors.

 

Thanks..

Tags: T-SQL, SQL, ErrorHandling, SQLSERVER-2005,


Ashish Gilhotra
31 · 6% · 1776
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]