DEFENSIVE PROGRAMMING

Contents
Error handling ......................................................................................................................................... 1
Introduction ........................................................................................................................................ 1
Retrieving error information ............................................................................................................... 2
@@RowCount ........................................................................................................................................ 2
References .............................................................................................................................................. 3
Error handling
Introduction
The Try..Catch implements error handling for Transact-SQL that is similar to the exception handling in the
Microsoft Visual C# and Microsoft Visual C++ languages. 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.

The syntax is as follows:

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
[ ; ]

Begin Try
--The_Query_for_which_we_need_to_do_the_ Error_Handling
End Try
Begin Catch
/*
If there is some error in the query within the Try block, this flow
will be passed to this Catch block.
*/
End catch

A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the
database connection.

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.

A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of
Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of
Transact-SQL statements and cannot span an IF…ELSE construct.

If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has
finished running, control passes to the statement immediately after the associated END CATCH statement. If
there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated
CATCH block. 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.

When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH
statement. Errors trapped by a CATCH block are not returned to the calling application. 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 and PRINT statements.

TRY…CATCH constructs can be nested. Either a TRY block or a CATCH block can contain nested TRY…CATCH
constructs. For example, a CATCH block can contain an embedded TRY…CATCH construct to handle errors
encountered by the CATCH code.

1|Page
Retrieving error information
In the scope of a CATCH block, the following system functions can be used to obtain information about the error
that caused the CATCH block to be executed:
 ERROR_NUMBER() returns the number of the error.
 ERROR_SEVERITY() returns the severity.
 ERROR_STATE() returns the error state number.
 ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
 ERROR_LINE() returns the line number inside the routine that caused the error.
 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.
These functions return NULL if they are called outside the scope of the CATCH block. Error information can be
retrieved by using these functions from anywhere within the scope of the CATCH block. For example, the
following script shows a stored procedure that contains error-handling functions. In the CATCH block of
a TRY…CATCH construct, the stored procedure is called and information about the error is returned.

CREATE PROCEDURE ErrorHandlingExample

Executing the stored procedure (exec ErrorHandlingExample) gives the following results:
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
8134 16 1 ErrorHandlingExample 7 Divide by zero error encountered.

@@RowCount
Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion,
use ROWCOUNT_BIG.

USE AdventureWorks2012;
GO
UPDATE [Link]
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO

You could use this to distinguish between an update and an insert:

2|Page
update Customer
set CustomerName = @CustomerName,
CustomerEmail = @CustomerEmail
where CustomerId = @CustomerId

if @@ROWCOUNT = 0
insert into Customer(
CustomerName,
CustomerEmail)
values(@CustomerName,
@CustomerEmail)

Alternatively, to distinguish between an update and an insert in the same stored procedure, use the If exists:

if exists(select CustomerId
from Customer
where CustomerId = @CustomerId)
--the if exists will return true as soon as a record matching the criteris is returned
--hence if a customer with that customer ID exists, then the if statement will be true
--and an update must be performed
update Customer
set CustomerName = @CustomerName,
CustomerEmail = @CustomerEmail
where CustomerId = @CustomerId

else
insert into Customer(
CustomerName,
CustomerEmail)
values(@CustomerName,
@CustomerEmail)

References
1. Code Project ([Link])
2. MSDN Library

3|Page