The Throw Statement In Sql Server

Q.1. How are Errors Handled in T-SQL? What is The Relevance of The Throw Statement?

Ans: Before SQL Server 2005's introduction of the TRY/CATCH construct, a feature loosely based on.try/catch NET's structured exception handling model, error handling in T-SQL was very challenging to implement properly. If a problem arises anywhere inside the TRY block above it, the CATCH block provides you with a single place to code error-handling logic. Before TRY/CATCH, it was necessary to test the built-in system function @@ERROR after each operation to look for error conditions. In addition to the fact that developers (being humans) would frequently forget to test @@ERROR in every necessary place, causing many unhandled exceptions to go undetected, code became cluttered with the numerous @@ERROR tests.

The constant testing of @@ERROR was greatly improved by TRY/CATCH in SQL Server 2005, but (until now) RAISERROR was the only method for creating your errors. The new THROW statement again, taken from the throw in the.NET model is the suggested alternate method for raising exceptions in your T-SQL code.

Q.2. Describe The Uses of The Throw Statement?

Ans: There are two ways to use the new THROW statement. First, as we already mentioned, it can replace RAISERROR by enabling your code to produce errors when it discovers an unresolvable condition during processing. The THROW statement, which functions similarly to RAISERROR in this context, accepts variables for the error code, description, and state.

A more specialized application of THROW can only be used inside of a CATCH block and requires no parameters. In this case, the TRY block above experiences an unexpected error, which causes the CATCH block to be executed. You can handle common errors inside the CATCH block (such as logging them or rolling back a transaction) and then issuing a THROW statement without any parameters. The original error that occurred—complete with its code, message, severity, and state—will be thrown back up to the client so that the application can also catch and handle the error. You can implement a segmented exception-handling strategy between the database and application layers in this simple yet elegant way.

RAISERROR, in contrast, always generates a fresh error. Therefore, it is limited to raising a new error by using the values of the ERROR MESSAGE, ERROR SEVERITY, and ERROR STATE captured in the CATCH block to simulate the original error. Listing 2-42 shows how much more straightforward using THROW is for this purpose.

Listing 2-42 Using THROW inside a CATCH block to re-throw an error.

The original error (divided by zero) is then rethrown for the client to catch in this code's CATCH block, which also records error information to the ErrLog table.

Msg 8134, Level 16, State 1, Line 4 

Divide by zero error encountered.

To confirm that the error was logged by the CATCH block as expected before being re-thrown, query the ErrLog table.

SELECT * FROM ErrLog 

GO 

ErrAt                                              Severity                   ErrMsg 

---------------------------                    --------       ------------------------------------------ 

2012-02-30 14:14:35.3361250      16                Divide by zero error encountered.

Q.3. Compare Throw and Raisepror Statements?

Ans: The key distinctions between THROW and RAISERROR are listed in the following table.

Table 2-4 Comparing THROW and RAISERROR.

A user exception is an error you define for your application's use and has a code of 50000 or higher. System exceptions have error codes under 50000 and are defined by SQL Server. User exceptions can be created and raised using the new THROW statement, but not system exceptions. System exceptions can only be thrown using RAISERROR. However, note that the real original exception—even if it's a system exception—will be thrown when THROW is used inside a CATCH block to re-throw an exception from a TRY block (this was illustrated in the previous "divide by zero" example).

When RAISERROR is used without specifying an error code, SQL Server generates the error code 50000. It requests that you provide an ad-hoc message to go along with it. A user error code of 50000 or above and an ad-hoc message for the error are always required by the THROW statement. Consequently, the two statements that follow are the same:

THROW 50000, 'An error occurred querying the table.', 1; 

RAISERROR ('An error occurred querying the table.', 16, 1);

Both of these statements generate an error with the exact message text and the error code 50000, severity 16, state 1. However, there is no further overlap between the two keywords because different usages impose different rules (as summarised in Table 2-4). For instance, token substitution is only supported by RAISERROR:

RAISERROR ('An error occurred querying the %s table.', 16, 1, 'Customer');

Msg 50000, Level 16, State 1, Line 22 

An error occurred querying the Customer table.

Throw is short of comparable ability. Additionally, THROW will always produce an error with a severity level of 16, unlike RAISERROR, which allows you to specify any severity level. This is important because level 11 and higher errors are more severe than level 10 and lower. For instance:

RAISERROR ('An error occurred querying the table.', 10, 1); 

An error occurred querying the table.

The error is displayed in black rather than the customary red used for severity levels more than 10. It does not echo the error code, level, state, or line number because the severity is set to 10. THROW, on the other hand, cannot be used to indicate a minor error.The RAISERROR association with sys.messages is the final significant distinction between the two keywords. The call to sys.sp addmessage is specifically required by RAISERROR to define error messages for user error codes greater than 50000. According to what was previously stated, the RAISERROR syntax in our previous examples uses an error code of 50000. It is the only one officially supported that allows you to supply an ad-hoc message rather than using sys.messages.

Q.4. How to Define Custom User Error Messages for the Raisepror Statement?

Ans: A message for user error code 66666 is first (and only once) added to sys.messages. In addition to providing values for token replacements applied to the message's text in sys.messages, RAISERROR, then references the error by its code.

EXEC sys.sp_addmessage 66666, 16, 'There is already a %s named %s.';

RAISERROR(66666, 16, 1, 'cat', 'morris'); 

Msg 66666, Level 16, State 1, Line 34

There is already a cat named morris.

Q.5. How to Define Custom Error Messages for The Throw Statement?

Ans: With THROW, you can send any ad-hoc message text. As a result, unlike RAISERROR, you don't need to manage sys.messages separately. Still, THROW is unable to (directly) benefit from centrally managed error messages in sys.messages. Fortunately, if you want to use the same functionality with THROW, the FORMATMESSAGE function offers a workaround.  You must watch out to make sure the same error code is specified in both of the places where you need to refer to it one time for FORMATMESSAGE and once for THROW, as demonstrated here:

DECLARE @Message varchar(max) = FORMATMESSAGE(66666, 'dog', 'snoopy'); 

THROW 66666, @Message, 1; 

Msg 66666, Level 16, State 1, Line 40 

There is already a dog named snoopy.

Q.6. How to Use the ROW_NUMBER Function? What are the Drawbacks of This Function?

Ans: As suggested by its name, the ROW NUMBER function creates a subsequent number for every row in the result set that your query returned. The WHERE clause of an outer query can then use the value returned by the ROW NUMBER function to specify the desired page as the only one to return results for. Listing 2-43 provides an illustration of this method using the AdventureWorks2012 database. It returns the results from "page 3" of the query, which are rows 21 through 30 (assuming a page size of 10).

Listing 2-43 Returning paged results with a nested subquery and the ROW_NUMBER function.

These are the results:

(10 row(s) affected)

This is effective, but there are two undesirables in this. First, regardless of whether you want or need it, you must create a new column in your result set for the row number.Second, the syntax is a little awkward; the necessity of using nested SELECT statements, multiple ORDER BY clauses, and the alias "AS a" are all awkward and counterintuitive.

Q.7. What is The Function of OFFSET/ FETCH NEXT Syntax?

Ans: Let’s understand the function of OFFSET/FETCH NEXT syntax with the help of the following example.

Listing 2-44 Returning paged results with the OFFSET/FETCH NEXT syntax.

The results are as simple as they get—just mention your starting row using OFFSET and your page size with FETCH NEXT.

Title      FirstName LastName 

--------     -----------     ----------

NULL        Bailey        Adams 

Mr.               Ben         Adams        

NULL         Blake        Adams     

Ms.             Carla        Adams      

NULL         Carlos        Adams     

NULL         Charles      Adams 

NULL          Chloe         Adams 

NULL        Connor        Adams 

NULL      Courtney       Adams 

NULL         Dalton        Adams 

(10 row(s) affected)

As you can see, this query produces the same "page" as the query that previously used the ROW NUMBER function, but neither the creation of a row number column nor the coding of a nested subquery was required. Additionally, OFFSET/FETCH NEXT performs significantly faster than the pre-SQL Server 2005 hacks using stored procedures and temp tables, as well as slightly faster than using ROW NUMBER. You can combine OFFSET/FETCH and ROW NUMBER if you still want row numbers in your result set by changing the SELECT statement in Listing 2-44 to:

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY LastName, FirstName), Title, FirstName, LastName

FROM Person.Person 

ORDER BY LastName, FirstName 

OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY

This query yields identical results in Listing 2-43, but it does not necessitate coding the subquery used in that listing. Also, remember that OFFSET can skip a predetermined number of rows and return all remaining rows even when FETCH NEXT is not specified.

Conclusion

We have discussed THROW and RAISEPROR statements. Consider doing an online SQL Certification to learn more about these statements.