stored procedure exception handling in asp.net

Question

Do i need to use try catch in my stored procedure to know where exactly in my procedure error has occured or can i achieve the same if i useSqlConnection.BeginTransaction in my ASP.NET form or simple try catch may be...???

I tried implementing try catch in my stored procedure..i m using sql server 2005 but it does not know TRY and CATCH keywords or ERROR_MESSAGE() function...i also installed sql server 2005 service manager but still it does not work..

if an error is thrown using RAISERROR method will the exception will be shown in my ASP.NET form??

Answer 1

Surround the DB call in your code with the try-catch... try and put it at the level of the code-behind. If you are using a data access layer, put a try-catch there also, with a single throw statement. Then handle it in the code-behind when it bubbles up.

Answer 2

To use TRY...CATCH within SQL Server look this manual: http://msdn.microsoft.com/en-us/library/ms175976.aspx

BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
          [ { sql_statement | statement_block } ]
END CATCH

You can catch the exception using catch (SqlException ex). You will find it in the System.Data.SqlClient namespace. You can also check the severity level.