Dealing with Errors

As with any other action against a database, deleting rows may end with errors. An error may range from a simple cant delete announcement to a catastrophic server failure. The level and severity values for errors have been discussed previously. The following information involves the specifics of delete actions.

Data Does Not Exist

Based on a discussion earlier in this chapter, you are familiar with what happens when you try to delete data using a WHERE clause that has no matches within the table: absolutely nothing happens. The delete instruction performs the action, but the command has nothing to do because there are no rows that match the WHERE clause. However, this fact might go unnoticed because Microsoft SQL Server 2005 does not interpret it as an error.

Execute the sentence that follows :

 DELETE FROM [AdventureWorks].[Sales].[SalesReason]       WHERE SalesReasonID=100 

You will receive the message below because there is no row in the SalesReason table with Id equal to 100.

 (0 row(s) affected) 

If you base your application development on trapping errors in your database actions, you must add your own error for this action. You can add your own error and error messages to SQL Server 2005 to raise specific information by using the sp_addmessage stored procedure.

Sp_addmessage takes the following parameters shown in Table 11-1.

Table 11-1: Parameters for the sp_addmessage Stored Procedure

Name	Content
@msgnum	Integer identifying your message. This value must be between 50,001 and 2,147,483,647. It is a good practice to use values in a defined range for each application.
@severity	Severity level. If you create your own error for something that your code or the user can solve, you should specify a value between 11 and 16.
@ msgtext	Text to display when this error is raised
@lang	Language used in the msgtext. This allows you to add multiple messages for the same error, depending on the users language. If this value is NULL, then the default language of your actual connection session will be used.
@with_log	If TRUE, the error will be written into the Application log when it is raised.

The following example uses a number greater than 50,001 for the message.

 sp_addmessage 51001,16,'No rows deleted','us_english',FALSE 

You should always use stored procedures to delete rows and raise the error when nothing has been deleted.

 ALTER PROCEDURE [Sales].[SalesReason_DeleteChecked]    @SalesReasonId int,    @CheckStamp bigint,    @DeletedRecords int=null OUTPUT AS BEGIN    DELETE Sales.SalesReason       WHERE SalesReasonID=@SalesReasonId       AND cast(Lastversion AS bigint)=@CheckStamp    SET @DeletedRecords=@@ROWCOUNT -- Raise an error when no row was deleted IF @DeletedRecords=0    RAISERROR (51001,16,1) END 

Data to Be Deleted Is Related to Other Tables

This is probably the most common error you will encounter during the life of your applications. You must therefore trap the error to manage it appropriately.

Execute the following code:

 DELETE FROM [AdventureWorks].[Sales].[SalesTerritory]       WHERE territoryId=1 

You will receive the following error:

 Msg 547, Level 16, State 0, Line 1 The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeader_Sales Territory_TerritoryID". The conflict occurred in database "AdventureWorks", table "Sales.SalesOrderHeader", column 'TerritoryID'. The statement has been terminated. 

Notice that this is a Level 16 error, indicating that the user can solve the problem. With this level, you can trap the error in your own application code. Because the user needs to understand the mistake, you can provide clearer information than the information provided by SQL Server 2005 (which is more technical information).

Other Errors

As in any SQL Server sentence, you may receive other errors, such as syntax or system failure errors. You must manage each error properly. Syntax errors will be a Level 16 error, which is the same level as referential integrity errors or the errors you created for DELETE sentences that do not affect any records.