ErrorHandling in StoredProcedure

Question

Hi All, Please can any one explain how to do Error handling in sqlserver? can any one explain with one realtime scenario....

Vipan Sharma · Answer

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 Trivedi · Answer

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 Mohapatra · Answer

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/

ErrorHandling in StoredProcedure

Answers (3)