ErrorHandling in StoredProcedure

jayachandra b

10y1.3k

Hi All,

Please can any one explain how to do Error handling in sqlserver?

can any one explain with one realtime scenario....

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vipan Sharma10y ago
Hello,

We can use try-catch block to handle exception in SQL Server.

TRY..CATCH Syntax

BEGIN TRY
     --T-SQL statements
     --or T-SQL statement blocks
END TRY
BEGIN CATCH
    --T-SQL statements
    --or T-SQL statement blocks
END CATCH

Exception handling example

BEGIN TRY
      DECLARE @num INT, @msg varchar(200)
      ---- Divide by zero to generate Error
     SET @num = 5/0
     PRINT 'This will not execute'
END TRY
BEGIN CATCH
      PRINT 'Error occured that is'
      set @msg=(SELECT ERROR_MESSAGE())
       print @msg;
END CATCH
GO

For more information . Kindly Visit on Original link of above information :

http://www.dotnet-tricks.com/Tutorial/sqlserver/O3P3120412-SQL-Server-Exception-Handling-by-TRY%E2%80%A6CATCH.html
Jignesh Trivedi10y ago
Hi,

Using TRY....CATCH block, we can do the exception handling in SQL server.

please refer
https://www.simple-talk.com/sql/database-administration/handling-errors-in-sql-server-2012/

hope this will help you.
Manas Mohapatra10y ago
We can use try-catch block to handle exception in SQL Server.

Here is a example: We are going to divide 1 by 0 which throws exception due to infinite value.

BEGIN TRY
   SELECT 1/0
END TRY

BEGIN CATCH
   SELECT ErrNumber = ERROR_NUMBER(),
      Severity = ERROR_SEVERITY(), [State] = ERROR_STATE(),
      [LineNo] = ERROR_LINE(), [Message] = ERROR_MESSAGE()
END CATCH

Output

ErrNumber ErrorSeverity ErrorState LineNo Message
8134 16 1 3 Divide by zero error encountered.

Go through below links to collect more knowledge on this:

http://www.dotnet-tricks.com/Tutorial/sqlserver/O3P3120412-SQL-Server-Exception-Handling-by-TRY%E2%80%A6CATCH.html

http://www.codeproject.com/Articles/38211/Exception-Handling-in-SQL-Server

https://www.simple-talk.com/sql/database-administration/handling-errors-in-sql-server-2012/