Microsoft® SQL Server® 2012 Bible

424

Part IV: Programming with T-SQL

AS

BEGIN TRY

SELECT 3/0

END TRY

BEGIN CATCH

THROW 50002,'CalledProc Raiserror',1

END CATCH

Go

EXEC TopProc

Result:

Msg 50001, Level 16, State 1, Procedure TopProc, Line 7

TopProc Raiserror

T-SQL Fatal Errors

If T-SQL encounters a fatal error, then the batch immediately aborts without giving you the

opportunity to test @@Error, handle the error, or correct the situation.

Fatal errors are rare enough that they shouldn’t pose much of a problem. Generally, if the

code works once, then it should continue to work unless the schema is changed or SQL

Server is reconfi gured. The most common fatal errors are those caused by the following:

■ (^) Data-type incompatibilities
■ (^) Unavailable SQL Server resources
■ (^) SQL Server advanced settings that are incompatible with certain tasks
■ (^) Missing objects or misspelled object names
For a list of most of the fatal error messages, run the following query:
SELECT message_id, severity, language_id, text
FROM master.sys.messages
WHERE language_id = 1033 -- US English
AND severity >= 19
ORDER BY severity, message_id;
Try.. .Catch does a good job of handling typical day-to-day user errors, such as
constraint-violation errors. Nevertheless, to be safe, front-end application developers
should also include error-handling code in their programs.

Bulk Operations

Often, DBAs need to load copious amounts of data quickly — whether it’s a nightly data

load or a conversion from comma-delimited text fi les. When a few hundred megabytes of

c16.indd 424c16.indd 424 7/30/2012 5:38:12 PM7/30/2012 5:38:12 PM

http://www.it-ebooks.info