SQL Interview Questions and Answers - Part 22
Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by below link:
Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.
Introduction
This is the 22nd part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.
I will highly recommend to please read the previous parts over here before continuing to the current part:
SQL Interview Questions and Answers - Part 22
Q201. How can you Handle Exceptions in SQL? Can you use the TRY/CATCH in SQL?
Unlike C# and other programming languages whenever an exception occurred in SQL, it displays the exception message and then continues the program execution. But sometimes we want to stop the program execution when an error or exceptions occurred in SQL.
Handling an error in SQL Server means stopping the execution of the statements which are related to the error. Error handling in SQL Server gives us control over the Transact-SQL code.
Before SQL Server 2005, we were using the RAISERROR() function and @@Error function to handle the exceptions.
But with the introduction of SQL Server 2005, Microsoft has introduced the Try-Catch construct blocks to handle the exceptions like other programming languages as well. But remember there is no Finally block like C# here.
Q202. What is the TRY-CATCH Exception Handling Mechanism in SQL?
From SQL Server 2005, we can use TRY and CATCH blocks in the stored procedure and trigger. But we cannot use the TRY-CATCH implementation within a user-defined function.TRY...CATCH constructs catch unhandled errors from stored procedures or triggers executed by the code in the TRY block.
Use the TRY...CATCH construct to handle errors. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.
TRY Block:
The SQL statements which can have the possibility to throw an exception need to be placed in between the BEGIN TRY and END TRY blocks.
A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
CATCH Block:
If there is an exception that occurred in the TRY block, then the control immediately moves to the Corresponding CATCH block.
If there is no exception occurring in the TRY block, then the CATCH block is simply skipped, and the statements which are present after the CATCH block are going to be executed.
But please note that unlike C# errors trapped by a CATCH block are never returned to the calling application implicitly.
If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR, THROW and PRINT statements explicitly.
BEGIN TRY
SELECT 1 / 0 AS Result;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
Errors encountered in a CATCH block are treated like errors generated anywhere else. If the CATCH block contains a nested TRY...CATCH construct, any error in the nested TRY block will pass control to the nested CATCH block.
If there is no nested TRY...CATCH construct, the error is passed back to the caller.
The CATCH block also supports additional system functions like ERROR_LINE, ERROR_NUMBER, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE that return more error information than @@ERROR.
TRY-CATCH Fundamental Points:
A TRY-CATCH construct only catches all execution errors that have a severity higher than 10 that do not close the database connection.
TRY...CATCH constructs can be nested. Either a TRY block or a CATCH block can contain nested TRY...CATCH constructs.
A TRY-CATCH construct cannot span multiple batches or multiple blocks of Transact-SQL statements. Means a TRY...CATCH construct cannot span two BEGIN...END blocks of Transact-SQL statements and cannot span an IF...ELSE construct.
If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger.
GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.
Limitation of SQL TRY/CATCH Constructs:
A TRY/CATCH construct does not trap the following conditions:
- Warnings or informational messages that have a severity of 10 or lower.
- Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session.
- If an error occurs that has a severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error.
- Attentions, such as client-interrupt requests or broken client connections.
- When the session is ended by a system administrator by using the KILL statement.
Also, the following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY...CATCH construct:
- Compile errors, such as syntax errors, that prevent a batch from running.
- Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
- Object name resolution errors
These errors are returned to the level that ran the batch, stored procedure, or trigger.
If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY...CATCH construct and will be handled by the associated CATCH block.
Running the SELECT statement inside a stored procedure will cause the error to occur at a level lower than the TRY block. The error will be handled by the TRY...CATCH construct.
Q203. What is the @@ERROR function in SQL?
In SQL Server 2000, in order to detect errors, we use the @@Error system function. The @@ERROR returns the error number for the last Transact-SQL statement executed.
@@ERROR returns 0 if the previous Transact-SQL statement encountered no errors. Returns an error number if the previous statement encountered an error. @@ERROR is set to 0 by default for messages with severity from 1 through 10.
If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages. So by using the @@ERROR you can detect a specific error.
The @@ERROR is cleared and reset on each statement execution. So need to check it immediately following the statement being verified, or save it to a local variable that can be checked later.
IF @@ERROR <> 0
PRINT N'Error Occured';
ELSE
PRINT N'Success';
The TRY...CATCH also supports an ERROR_NUMBER() function that is not limited to returning the error number in the statement immediately after the statement that generated an error.
Apart from @@ERROR, you can also use other system functions like ERROR_MESSAGE, ERROR_SEVERITY, etc in the CATCH block to get more error information than @@ERROR.
Q204. What is ERROR_MESSAGE() function in SQL?
The ERROR_MESSAGE is a system-defined error statement in SQL Server. This method is used to display what type of error has occurred in the TRY block.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
SQL Server also provides some other built-in functions that we can use in the scope of a CATCH block which is used to retrieve more information about the error that occurred:
ERROR_NUMBER(): It returns the error number.
ERROR_PROCEDURE(): It returns the name of the stored procedure or trigger where the error occurred.
ERROR_STATE(): It returns the error state number.
ERROR_SEVERITY(): It returns the severity.
ERROR_LINE(): It returns the line number inside the routine that caused the error.
These functions return NULL if they are called outside the scope of the CATCH block.
Q205. What is the use of the RAISERROR () function in SQL?
The RAISERROR() is a system-defined function that returns an error message back to the calling application. The RAISERROR() generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically.
The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct. If you use the RAISEERROR() function without TRY and CATCH blocks, the RAISERROR statement after raising the error will still continue the execution of the program. But if it is used under the TRY block, it will jump directly to CATCH block from where the error got raised.
The error is returned to the caller if RAISERROR is run:
- Outside the scope of any TRY block.
- With a severity of 10 or lower in a TRY block.
- With a severity of 20 or higher that terminates the database connection.
The CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block by using system functions such as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information.
The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. So the values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions.
The RAISERROR System-defined SQL Function takes 3 main parameters.
RAISERROR(‘Error Message’, ErrorSeverity, ErrorState)
Error Message: The custom error message that you want to display whenever the exception is raised.
Error Severity: When we are returning any custom errors in SQL Server, we need to set the ErrorSeverity level as 16, which indicates this is a general error and this error can be corrected by the user.
Error State: The ErrorState is also an integer value between 1 and 255. The RAISERROR() function can only generate custom errors if you set the Error State value between 1 to 127.
By using WITH LOG option in the RAISERROR statement we can record the error message in the SQL Server log file so that if the errors are fatal database administrator can take care of fixing those errors. If the severity of the error is greater than 20 specifying the With Log option is mandatory.
RAISERROR (‘DIVISOR CAN NOT BE ZERO’, 16, 1) WITH LOG
You can also substitute values into the error message to make the error message as dynamic:
RAISERROR (‘THE NUMBER %d CAN NOT BE DIVIDED BY %d’, 16, 1, @No1, @No2) WITH LOG
RAISEERROR() Fundamental Points:
- RAISEERROR can be used in both TRY and Catch blocks.
- The RAISERROR statement does not honor SET XACT_ABORT. So every new application should use THROW instead of the RAISERROR function.
- RAISERROR only generates errors with state from 1 to 127. Because the Database Engine may raise errors with state 0.
- When RAISERROR is run with a severity of 11 or higher in a TRY block, only then it transfer control to the associated CATCH block.
- @@ERROR is set to 0 by default for messages with severity from 1 through 10.
RAISERROR can be used as an alternative to PRINT to return messages to calling applications. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block.
Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.
Severity levels from 0 through 18 can be specified by any user. But Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.
Using SysMessage Table:
We can raise an error without giving the error message hardcoded in the RAISERROR statement but in place of the error message we need the specify the error id and to specify the error id we need to record that error id with the error message in the SysMessage table by using the system defined procedure “SP_ADDMESSAGE”.
SP_ADDMESSAGE <error id>, <severity>, <error message>
EXEC sp_Addmessage 51000, 16, ‘DIVIDE BY ONE ERROR ENCOUNTERED’
Now you can use it like this :
RAISERROR (51000,16, 1)WITH LOG
You can Delete the error messages from the sys messages table as well:
SP_DROPMESSAGE <error id>
EXEC sp_dropMessage 51000
Q206. What is the use of the THROW Statement in SQL?
The THROW statement raises an exception and transfers execution to a CATCH block of a TRY...CATCH construct in SQL Server.
In the case of THROW, we need to specify both the error_id and error message to raise the error whereas in the case of RAISERROR we can specify either id or message.
THROW error_id 'message' state
If the id is not specified default error id is 50000 but if we want to specify only the error id first we need to add the error message in the SysMessage table by specifying a unique id to the table.
The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
If a TRY...CATCH construct is not available, and the statement batch is terminated. The line number and procedure where the exception is raised are set. The severity is set to 16.
THROW 51000, 'The record does not found.', 1;
If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statement causes the statement batch to be terminated.
BEGIN CATCH
PRINT 'In catch block.';
THROW;
END CATCH;
The % symbol is a reserved character in the message text of a THROW statement and must be escaped. Double the % character to return % as part of the message text.
THROW 51000, 'The increase exceeded 15%% of the original value.', 1
We can use the FORMATMESSAGE() function with THROW to throw a customized error message. For that first, we need to create a user-defined error message by using sp_addmessage.
Because the THROW statement does not allow for substitution parameters in the message parameter in the way that RAISERROR does, the FORMATMESSAGE function is used to pass the three-parameter values expected by error message 60000.
EXEC sys.sp_addmessage
@msgnum = 60000
,@severity = 16
,@msgtext = N'This is a test message with one numeric parameter (%d), one string parameter (%s), and another string parameter (%s).'
,@lang = 'us_english';
GO
Now you can use this:
DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(60000, 500, N'First string', N'second string');
THROW 60000, @msg, 1;
Q207. What is the difference between the RAISERROR function and the THROW statement?
If we use any of the two statements in a program for raising a custom error without TRY and CATCH blocks, the RAISERROR statement after raising the error will still continue the execution of the program whereas the throw statement will terminate the program abnormally on that line.
But if they are used under the TRY block both will behave in the same way that it will jump directly to the CATCH block from where the error got raised.
RAISERROR vs THROW:
In the case of THROW, we need to specify both the error_id and error_message to raise the error whereas in the case of RAISERROR we can specify either id or message.
If the id is not specified default error id is 50000 but if we want to specify only the error id first we need to add the error message in the SysMessage table by specifying a unique id to the table.
The RAISERROR statement will give an option of specifying the ERROR SEVERITY Level of the error message. But we don’t have this option in the case of the THROW statement where all error messages will have a default ERROR SEVERITY level of 16.
THROW statement does not allow for substitution parameters in the message parameter in the way that RAISERROR does. So FORMATMESSAGE function is used to pass the three-parameter values expected by error message 60000 in the THROW statement.
In the case of RAISERROR, there is a chance of recording the error message into the server log file by using the WITH LOG option whereas we cannot do this in the case of a THROW statement.
The RAISERROR statement does not honor SET XACT_ABORT. But the THROW statement does, so every new application should use THROW instead of RAISERROR.
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages. On the other hand, the error_number parameter in THROW does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles in RAISEERROR(). But the message parameter in THROW does not accept printf-style formatting.
In RAISEERROR the severity parameter specifies the severity of the exception. While there is no severity parameter in THROW, When THROW is used to initiate the exception, the severity is always set to 16.
However, when THROW is used to re-throw an existing exception, the severity is set to that exception's severity level.
Q208. What are the Predefined Errors and their formats in SQL?
We can find the information of all predefined errors under the table SysMessages. Whenever an error occurs under a program the system displays an error message telling us the problem encountered in the code.
Every error that occurs in the program is associated with four attributes:
Message 8134 (Error Number), Level 16(SEVERITY Level), State 1 (state), Divide by Zero error encountered (Error Message)
Error Number: The Error number is a unique identifier given for each and every error that occurs in SQL Server.
This value will be below 50,000 for predefined errors and must be above or equal to 50,000 for errors defined by the user.
While raising custom errors, if we don’t specify the error number, then by default it will set the Error Number as 50000.
Error Message: It is a piece of brief information describing the error that occurred which should be maxing from 2047 characters.
Severity Level: This tells about the importance of the error which can be ranging from 0 to 24.
Error State: It is an arbitrary value that is not that important and can be ranging between 0 to 127. We use this whenever the same error has to occur in multiple places.
Q209. How can you generate user-defined custom error messages in SQL?
Generally, errors are raised in a program for predefined reasons like dividing a number by zero, violation of primary key, violation of check, violation of referential integrity, etc.
But if you want then you can also raise a custom user-defined error in your programs in two different ways.
- RAISEERROR() system function
- THROW Statement
We can use RAISEERROR() function or THROW statement to generate user-defined error messages.
However, if we use any of the two statements in a program for raising a custom error without TRY and CATCH blocks, the RAISERROR statement after raising the error will still continue the execution of the program whereas the throw statement will terminate the program abnormally on that line.
But if they are used under the TRY block both will behave in the same way that it will jump directly to the CATCH block from where the error got raised.
By using WITH LOG option in the RAISERROR statement we can record the error message in the SQL Server log file so that if the errors are fatal database administrator can take care of fixing those errors. If the severity of the error is greater than 20 specifying the With Log option is mandatory.
Q210. What is the Error Severity in SQL? What is its use?
When an error is raised by the SQL Server Database Engine, the severity of the error indicates the type of problem encountered by the SQL Server. A TRY...CATCH construct catches all execution errors with a severity greater than 10 that do not terminate the database connection.
Errors with severity from 0 through 10 are informational messages and do not cause execution to jump from the CATCH block of a TRY...CATCH construct.
Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.
The ERROR_SEVERITY() system function can be used to retrieve the severity of the error that caused the CATCH block of a TRY...CATCH construct to be run. But it must be used within the CATCH block. It will return NULL if called outside the scope of a CATCH block.
The sp_addmessage can be used to add user-defined error messages with severities from 1 through 25 to the sys.messages catalog view. Then These user-defined error messages can be used by the RAISERROR function.
RAISERROR can be used to generate user-defined error messages with severities from 1 through 25. RAISERROR can reference a user-defined error message stored in the sys.messages catalog view or build a message dynamically.
When using the user-defined error message in sys.messages while generating an error, the severity specified by RAISERROR overrides the severity specified in sys.messages.
System-Defined Severity Levels:
There are different levels of system-defined severity from 0 to 24:
0-9: Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9.
11 to 16: Indicates these errors can be created by the user.
13: Indicates transaction deadlock errors.
14: Indicates security-related errors, such as permission denied.
15: Indicates syntax errors in the Transact-SQL command.
17: Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.
18: Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained.
19: Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch.
20-24: Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running.
To Be Continued Part-23...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
No comments:
Post a Comment
Please do not enter any HTML. JavaScript or spam link in the comment box.