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.