Microsoft® SQL Server® 2012 Bible

414

Part IV: Programming with T-SQL

operation failed, which might indicate a data, logic, or business rule problem. The

@@rowCount system function is useful for checking the effectiveness of the query.

The reset issue that affects @@error also affects @@rowcount.

The following batch uses @@rowcount to check for rows updated. The failure results from

the incorrect WHERE clause condition. No row with DepartmentID = 100 exists.

@@rowcount is used to detect the query failure:

USE AdventureWorks2012;

GO

UPDATE HumanResources.Department

SET Name = 'Ministry of Silly Walks'

WHERE DepartmentID = 100;

IF @@rowCount = 0

BEGIN

-- error handling code

PRINT 'no rows affected'

END;

Result:

no rows affected

To capture both the @@error and the @@rowcount functions, use a SELECT statement

with two variables:

SELECT @err = @@error, @rcount = @@rowcount

Raiserror

To return custom error messages to the calling procedure or front-end application, you can

use the RAISERROR command. It has four useful features:

■ Specifi es the severity level

■ (^) Dynamically modifi es the error message
■ Uses serverwide stored messages
■ (^) May optionally log the error to the event log
The syntax for RAISERROR includes parameters for the severity level, state (seldom used),
and message-string arguments:
RAISERROR (
message or number, severity, state, optional arguments
) WITH LOG;
c16.indd 414c16.indd 414 7/30/2012 5:38:10 PM7/30/2012 5:38:10 PM
http://www.it-ebooks.info