Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
SQL Server MVP Deep Dives- P4: Each year Microsoft invites all the MVPs from every technology and country to Redmond for an MVP Summit—all top secret—“don’t tweet what you see!” During the MVP Summit, each product team holds a series of presentations where they explain their technologies, share their vision, and listen to some honest feedback. | 76 Chapter 6 Error handling in SQL Server and applications The ERROR_STATE function can be used to determine the error state. Some system error messages can be raised at different points in the SQL Server engine. SQL Server uses the error state to differentiate when these errors are raised. The last two properties of an error are the line number and the name of the stored procedure where the error occurred. These can be returned using the ERROR_LINE function and the ERROR_PROCEDURE function respectively. The ERROR_PROCEDURE function will return NULL if the error occurs outside a stored procedure. Listing 4 is an example of these last two functions inside a stored procedure. Listing 4 ERROR_LINE and ERROR_PROCEDURE functions in a stored procedure CREATE PROCEDURE ChildError AS BEGIN RAISERROR My Error 11 1 END GO CREATE PROCEDURE ParentError AS BEGIN EXEC ChildError END GO BEGIN TRY EXEC ParentError END TRY BEGIN CATCH SELECT Error_Line ERROR_LINE Error_Proc ERROR_PROCEDURE END CATCH This returns the following result Error_Line Error_Proc 4 ChildError Let s look now at how we can generate our own custom error messages. Generate your own errors using RAISERROR The RAISERROR function can be used to generate SQL Server errors and initiate any error processing. The basic use of RAISERROR for a dynamic error looks like this RAISERROR Invalid Customer 11 1 This returns the following when run in SQL Server Management Studio Msg 50000 Level 11 State 1 Line 1 Invalid Customer The first parameter is the custom error message. The second is the severity or level . Remember that 11 is the minimum severity that will cause a CATCH block to fire. The last parameter is the error state. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Handling errors inside SQL Server 77 RAISERROR can also be used to return user-created error messages. The code in listing 5 illustrates this. Listing 5 Returning user-created error messages with RAISERROR EXEC sp_addmessage
