SQL Server Error Handling Interview Question and Answers

In this article I will share some frequently asked Error Handling interview questions and answers in SQL server.

1. How will you Handle Error in SQL SERVER ?

Error handling for T-SQL similar to C#, Java, C++. Group of SQL statement enclosed
Inside try block, if error occurs in try block control is passed to another Sql.

Before SQL server 2012 release tray…catch block used to handle error with RAISERROR.

SQL Server 2012 introduced the THROW statement. You now have a replacement for RAISERROR, the THROW statement, which makes it easier than ever to capture the error-related data.

More.. http://codechef4u.com/post/2015/04/13/throw-t-sql

http://codechef4u.com/post/2015/04/06/raiserror

2. What is RAISEERROR?

Used to return error message back to application, system warning or error message generated by Sql server database engine.

Syntax:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

 More detail… http://codechef4u.com/post/2015/04/06/raiserror

3. Explain Try...Catch with sql server?

Error handling for T-SQL similar to C#, Java, C++. Group of SQL statement enclosed
Inside try block, if error occurs in try block control is passed to another Sql. Statement enclosed in catch block.

Syntax:

BEGIN TRY
     { T-Sql_ statement | statement_block }
END TRY
BEGIN CATCH
     [ { T-Sql _statement | statement_block } ]
END CATCH
[ ; ]

More info… http://codechef4u.com/post/2015/04/13/try-catch

4. Explain THROW statement in sql server 2008?

THROW statement raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server (SQL Server 2012+)

You don’t have to specify any parameters and the results are more accurate. You simply include the statement as is in the CATCH block.

 more info...http://www.codechef4u.com/post/2015/04/13/throw-t-sql.aspx

5. Explain following error properties?

ERROR_NUMBER():

The number assigned to the error.

ERROR_LINE():

The line number inside the routine that caused the error.

ERROR_MESSAGE():

The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.

ERROR_SEVERITY():

The error’s severity.

ERROR_STATE():

The error’s state number.

ERROR_PROCEDURE():

The name of the stored procedure or trigger that generated the error.

6. Explain Error and Transaction Handling in SQL Server?

You can use try-catch block for Error and Transaction Handling, inside try block write your sql statements or queries check "@@TRANCOUNT > 0"  if that’s is greater than "0" rollback transaction in catch block else commit transaction.

More… http://www.codechef4u.com/post/2015/04/06/raiserror

http://codechef4u.com/post/2015/04/13/try-catch

7. Can we use two or more catch block with associated one try block?

No that is not allowed.

8. Can we use TRY..CATCH block with transaction?

Yes ,TRY..CATCH blocks can be used with transactions.

9. Can we use try catch block inside user defined function?

No, TRY...CATCH construct cannot be used in user defined function.