SQL Error Handling

-- Jeffrey R. Schenk, MCDBA, MCSE, MCSA

-- This article is formatted as Transact-SQL code -
-- ready to be pasted right into Query Analyzer,
-- for example, comments and all.

---------------------------------------------------------------------
-- Before We Begin: Soapboxing On the Importance of Error Handling --
---------------------------------------------------------------------

-- S1. Why we need to trap errors in code
-- S2. Why it's impractical to implement error-checking later
-- S3. The pitfalls of indifference toward error-checking
-- S4. SQL Server doesn't care if your code is readable...

----------------------------------
-- What This Session Will Cover --
----------------------------------

-- 1. Why the @@ERROR global variable is our friend
-- 2. How to RETURN an error success (or failure) code
-- 3. How to OUTPUT non-integer error/decision data
-- 4. When to SELECT error data into resultsets
-- 5. Using RAISERROR to create some errors that ain't
-- 6. How to bit-masquerade multiple errors into a single integer
-- 7. Hunting down pesky errors in dynamically-constructed SQL
-- 8. Errors in ancient command-line stuff (here in real world)

-----------------------------------
-- What This Session Won't Cover --
-----------------------------------

-- 1. Any error-trapping outside the TRANSACT SQL Environment
-- A. DAO, RDO, ADO, ODBC, OLEDB, etc.

-- 2. Regurgitated Sql Server Online Books stuff.
-- 3. The litany of known errors in an otherwise wonderful product.
-- 4. The latest news on anything... I can't keep up with the old news!

----------------------------------------------------
-- Soapbox 1. Why we need to trap errors in code --
----------------------------------------------------

-- A. If you care about whether or not a thing worked,
-- you need to ask the question: did it work?

-- B. If you don't care whether or not a thing worked,
-- you needn't ask the question.

-- C. Most branches in code involve some form of the
-- question "Did things work as expected?"

--------------------------------------------------------------------------
-- Soapbox 2. Why it's impractical to implement error-checking "later" --
--------------------------------------------------------------------------

-- A. We'd freak if we found out that a commercial flight
-- planned to get off the ground first (they have a
-- deadline to meet), then deal with the pre-flight
-- checks later. Think of the liability... but we do
-- it with the code that runs billion-dollar enterprises
-- all the time. Go figure?

-- B. Code branches based on the "Did it work?" question
-- are an integral part of your code. Most code is
-- 20% doing the thing, and 80% figuring out how to
-- handle the mess gracefully when the answer is,
-- "Nope, it didn't work".

-- C. There'll be another crisis tomorrow that'll pull
-- you off your plans for later... I've heard tell
-- of an environment where there's lots of time to
-- fix things later, but I've never seen it.

--------------------------------------------------------------------
-- Soapbox 3. The pitfalls of indifference toward error-checking --
--------------------------------------------------------------------

-- A. Fault-intolerant (high maintenance) processes

-- B. Propagation of garbage code: You know those little
-- code snippets we all use... (the ones that spare us
-- from remembering how to do what we once did, the
-- ones that get cut-and-pasted from project to project)

-- C. Destruction of your reputation, the product's reputation,
-- your organization's reputation, etc.

-- D. Do you carry a beeper? Love spending your weekends
-- defending yourself and deflecting blame?

----------------------------------------------------------------------
-- Soapbox 4. SQL Server doesn't care if your code is readable... --
----------------------------------------------------------------------

-- A. I do.

-- B. While a little style, structure, and convention go
-- a long way toward making your code more palatable
-- to the rest of us... their importance transcends the
-- readability issue... in that some styles (especially
-- nesting) lend themselves to error-trapping.

-- C. Being a horse of habit, I find that sticking with
-- a set of conventions keeps me a better error-checker.

-- For example: I always try to build my code so
-- that I handle success conditions first, and
-- error conditions last. Technically, it doesn't
-- make a bit of difference, but it's easy to follow:

-----------------------------------------------------

IF (@@ERROR = 0) ------------------
BEGIN -- You Get What You Need --
---------------------------

PRINT 'I saw her today at the reception'

END ----------------------------------------
ELSE -- You can't always get what you want --
BEGIN ----------------------------------------

PRINT 'She was practiced in the art of deception'
END

--------------------------------------------------------


-------------------------------------------------------
-- 1. Why the @@ERROR global variable is our friend --
-------------------------------------------------------

-- A. The @@ERROR variable returns a success or failure
-- code for the most-recently executed SQL Statement.

-- B. Hint: It doesn't return the success or failure of
-- the last twenty statements, just the last one.

-- C. If you want to know whether or not something worked,
-- you need to check it immediately following the
-- execution of the statement, or:

-- D. You can capture the @@ERROR value now, but wait
-- to evaluate it until you're further along in code.

-- EXAMPLE:

--------------------------------
-- Simplistic Error Detection --
--------------------------------

SELECT DivideByZero = 1/0

-- Uncomment the following line to demo hiding errors
--SELECT TheGrandIllusion = 'Or How to Hide Your Mistakes'

IF (@@ERROR = 0) ------------------------------
BEGIN -- Here We Stand at the Great Divide --
---------------------------------------

PRINT 'Everything is wonderful, just wonderful'

END --------------------------
ELSE -- It Ain't in Colorado --
BEGIN --------------------------

PRINT 'Hey, Pilgrim, whatdaya think your doing?'
END

-------------------------------------------------------
-- Catch that Pesky Error Now, Deal With It Later :) --
-------------------------------------------------------

DECLARE @intStrikeOuts integer
SELECT @intStrikeOuts = 0 --Initialize before bitmasking

SELECT BatterBatterSwing = 'Flynn lets drive a single'
SELECT @intStrikeOuts = @@ERROR

SELECT BatterBatterSwing = 'Cooney dies at first '
SELECT 1/0 --Force an error here
SELECT @intStrikeOuts = @intStrikeOuts | @@ERROR

SELECT BatterBatterSwing = 'Johnnie safe at second'
SELECT @intStrikeOuts = @intStrikeOuts | @@ERROR

SELECT BatterBatterSwing = 'and Flynn a-hugging third'
SELECT @intStrikeOuts = @intStrikeOuts | @@ERROR

IF (@intStrikeOuts = 0) ----------------
BEGIN -- Somewhere Men are Laughing --
--------------------------------

PRINT 'Somewhere children shout!'

END ---------------------------------
ELSE -- There Is No Joy In Mudville --
BEGIN ---------------------------------

PRINT 'Mighty Casey has struck out!'
END

--------------------------------------------------------------
-- The Trick to Capturing the @@ERROR Global Variable Along --
-- With Other Global Variables (the @@ROWCOUNT for example) --
--------------------------------------------------------------

DECLARE @intRowCount integer
DECLARE @intErrorCode integer

SELECT
Song_ID, -- / 0, -- Force a divide by zero error
ArtistName,
SongTitle
FROM
Corporate.vwx_SongList
WHERE
SongTitle LIKE '%sunshine%'
ORDER BY
ArtistName

--The following lines will be uncommented for demo purposes
--SELECT @intRowCount = @@ROWCOUNT -- Wrong approach, clears error
--SELECT @intErrorCode = @@ERROR -- Wrong, again... clears rowcount
--SELECT @intRowCount = @@ROWCOUNT, @intErrorCode = @@ERROR -- It's a good thing.

IF ((@intErrorCode = 0) AND (@intRowCount > 0)) ----
BEGIN -- I'm The Happiest Girl In The Whole USA --
--------------------------------------------

PRINT 'Skip with me Sunshine'

END -----------------------------------
ELSE -- This is How the World Will Be --
BEGIN -----------------------------------

PRINT 'Everywhere I go, It Rains On Me'
END

------------------------------------------------------------
-- A word about what the global error variable won't trap --
-- (if you're running an older version of SQL Server) --
------------------------------------------------------------

EXEC ('
--SELECT 1/0 -- Force Error; demo that it is not trapped
SELECT SongTitle
FROM Songs
WHERE SongTitle LIKE ''%Tennessee%''
ORDER BY SongTitle DESC
')

IF (@@ERROR = 0) -------------------------------------
BEGIN -- Joy to the World, All the Boys and Girls --
----------------------------------------------

PRINT 'Joy to the fish-ies in the deep blue sea'

END ---------------------------------
ELSE -- I Ain't Never Been To Spain --
BEGIN ---------------------------------

PRINT 'But I''ve been to Oklahoma!'
END

----------------------------------------------------------
-- 2. How to RETURN an error success (or failure) code --
----------------------------------------------------------

-- A. Providing a RETURN Code is easy... it's providing
-- one that's useful that's the bigger challenge.

-- B. This section will focus on creating a return code,
-- and on how to read it on the calling end.

-- C. Building Cool RETURN codes will covered later in
-- in this document.

-- D. Return Codes are a handy for passing back specific
-- data on how well a procedure worked. This data can
-- be returned to another procedure, or an application.


--DROP PROCEDURE Corporate.usp_ReturnStuff

CREATE PROCEDURE usp_ReturnStuff

@strSongTitle varchar(100) = NULL
AS
SELECT *
FROM vwx_SongList
WHERE SongTitle LIKE '%' + @strSongTitle + '%'
ORDER BY SongTitle

IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
BEGIN
RETURN(0) --All's well that ends well.

END -----------------------------
ELSE -- No Song Found, or worse --
BEGIN -----------------------------

RETURN(1) --Things went wrong, bad wrong.
END
GO
---------------------------------------------------------
-- How to Snag the RETURN Code from a Called Procedure --
---------------------------------------------------------

DECLARE @intReturnStatus integer

EXEC Songs.Corporate.usp_ReturnStuff 'Saturday'

--EXEC @intReturnStatus = Songs.Corporate.usp_ReturnStuff 'Wednesday'

IF ((@@ERROR = 0) AND (@intReturnStatus = 0)) --
BEGIN -- I've Got a Peaceful, Easy Feeling ---
----------------------------------------

SELECT Goody = @intReturnStatus

END -----------------------------------
ELSE -- Did I Shave My Legs For This? --
BEGIN -----------------------------------

SELECT Arrrgh = @intReturnStatus
END

-------------------------------------------------------
-- 3. How to OUTPUT non-integer error/decision data --
-------------------------------------------------------

--DROP PROCEDURE Corporate.usp_OutputStuff

CREATE PROCEDURE Corporate.usp_OutputStuff

@strSongTitle varchar (100) = '%',
@datLastModified smalldatetime = NULL OUTPUT
AS

SELECT
@datLastModified = MAX(LastModified)
FROM
Corporate.Songs
WHERE
(SongTitle LIKE '%' + @strSongTitle + '%')

IF (@@ERROR = 0) RETURN(0) ELSE RETURN(1)

GO
--------------------------------------------------------
-- How to Read An OUTPUT Parameter (you might want to --
-- to evaluate it for some success/failure decision). --
--------------------------------------------------------

DECLARE @intReturnStatus integer
DECLARE @datNewestModDate smalldatetime

EXEC @intReturnStatus = Songs.Corporate.usp_OutputStuff

@strSongTitle = 'Blue Eyes Crying In The Rain',
@datLastModified = @datNewestModDate OUTPUT

SELECT
TheBigDate = @datNewestModDate,
TheStatusMan = @intReturnStatus

---------------------------------------------------
-- 4. When to SELECT error data into resultsets --
---------------------------------------------------

-- When you want to pass status codes to the client you might
-- try inserting a select statement at the close of a stored
-- procedure (that doesn't otherwise return multiple-row
-- results) to offer the status code up as a field of data.

--DROP PROCEDURE Corporate.usp_OfferStatusResults

CREATE PROCEDURE Corporate.usp_OfferStatusResults

AS
DECLARE @intLongestTitle integer
DECLARE @intShortestTitle integer

SELECT
@intLongestTitle = MAX(DATALENGTH(SongTitle)),
@intShortestTitle = MIN(DATALENGTH(SongTitle))
FROM
Corporate.vwx_SongList

IF (@@ERROR = 0) -----------------------------------
BEGIN -- Look What They've Done To My Song, Ma ---
--------------------------------------------

SELECT
LongestTitle = @intLongestTitle,
ShortestTitle = @intShortestTitle,
ReturnStatus = 0

RETURN(0)

END --------------------------------
ELSE -- Ribbon of Darkness Over Me --
BEGIN --------------------------------
SELECT
LongestTitle = NULL,
ShortestTitle = NULL,
ReturnStatus = 1

RETURN(1)
END

GO
---------------------------------
--Simulated Pass-Through Query --
---------------------------------

EXEC Songs.Corporate.usp_OfferStatusResults

-- For Those Who Care: The Longest Song Title Is
-- a five-parter (laid down on a single track) of
-- Rush's 2112 Album:

-- 2112: I: Overture/II: The Temples Of Syrinx/III: Discovery/IV: Presentation/V: Oracle: The Dream...

---------------------------------------------------
-- 5. Using RAISERROR to create some that ain't --
---------------------------------------------------

-- A. The RAISERROR command is often preferable to the print
-- command in that it is more flexible in it's use... And
-- (I think) easier to use once you are familiar with it's
-- basic syntax.

RAISERROR('Some Stuff I Wanna Get Said', -1, -1) --Default Severity, State
RAISERROR('Some Informational Stuff I Wanna Get Said', 10, -1) --Informational
RAISERROR('Some Serious Stuff I Wanna Get Said', 11, -1) --Raise an Error, slight
RAISERROR('Some Critical Stuff I Wanna Get Said', 18, -1) --Raise an Error, severe
RAISERROR('Some Stuff I''m Not Entitled to Get Said', 19, -1) --Raise an Error, drastic

-- B. Permanently creating a (possibly just informational) ERROR Message
-- to call upon later:

EXEC Master.dbo.sp_addmessage
50002, -- Message Number
10, -- Default Severity
'usp_MyProcedure -- Success(%d): %s', -- Message String
NULL, -- Language (Default)
TRUE, -- Log this error
REPLACE -- Replace any existing error message of this number
GO
EXEC Master.dbo.sp_addmessage
50003, -- Message Number
11, -- Default Severity
'usp_MyProcedure -- Failure(%d): %s', -- Message String
NULL, -- Language (Default)
TRUE, -- Log this error
REPLACE -- Replace any existing error message of this number
GO
-----------------------------------------------------------
-- Let's have a look at our newly-created error messages --
-----------------------------------------------------------

SELECT * FROM Master.dbo.sysmessages WHERE error > 50000

--------------------------------------------------------------
-- Now Let's Throw Our Boots on and RAISE some ..ugh ERRORS --
--------------------------------------------------------------

RAISERROR (50002, -1, -1, 0, 'Informational Error Raised By Jeff')
RAISERROR (50003, -1, -1, 64, 'Error Condition Raised By Jeff')
RAISERROR (50003, 10, -1, 64, 'Error Condition treated as information only')

--------------------------------------------------------------------
-- Review the Error Log from Enterprise Manager's Management Node --
--------------------------------------------------------------------

---------------------------------------------------------------------
-- 6. How to bit-masquerade multiple errors into a single integer --
---------------------------------------------------------------------

-- A quick Binary Number System Review

-- 0000000000000000 = 0
-- 0000000000000001 = 1
-- 0000000000000010 = 2
-- 0000000000000100 = 4
-- 0000000000001000 = 8
-- 0000000000010000 = 16
-- 0000000000100000 = 32
-- 0000000001000000 = 64
-- 0000000010000000 = 128
-- 0000000100000000 = 256
-- 0000001000000000 = 512
-- 0000010000000000 = 1024
-- 0000100000000000 = 2048
-- 0001000000000000 = 4096
-- 0010000000000000 = 8192
-- 0100000000000000 = 16384
-- 1000000000000000 = 32768

-- 8 Handy-dandy error flags built into a single byte (tiny integer)
-- 16 Handy-dandy error flags built into a small integer (as illustrated)

-----------------------------------------------------------------------
-- Creating a composite return code that illustrates multiple errors --
---------------------------------------------------------------------

DECLARE @intReturnCode integer
SELECT @intReturnCode = 0 -- initialize it, for later bit masking
-- Some fake error evaluation code follows

-- Fake File Error Detected

SELECT @intReturnCode = @intReturnCode | 1 -- Our routine's file error

-- Fake Insert Error Detected
SELECT @intReturnCode = @intReturnCode | 2 -- Our routine's insert error

-- Fake Insert Error Detected
SELECT @intReturnCode = @intReturnCode | 4 -- Our routine's update error

-- Fake Computational Error Detected
SELECT @intReturnCode = @intReturnCode | 64 -- Our routine's computation error

SELECT FourErrorsInOne = @intReturnCode

---------------------------------------------------------------------
-- Determine whether or not we were returned a computational error --
---------------------------------------------------------------------

IF((@intReturnCode & 64) = 64) --Value of computational component of error
BEGIN
SELECT Error_64_Detected = 'System does not compute, repeat, does not compute!'
END

IF((@intReturnCode & 32) = 32) -- Value of bogus error we want to detect
BEGIN
SELECT Error_32_Detected = 'Bogus error detected in sector B!'
END

------------------------------------------------------------------
-- 7. Hunting down pesky errors in dynamically-constructed SQL --
------------------------------------------------------------------

-- Wrapping your dynamically-create code into a temporary
-- stored procedure often helps pass data back and forth, cleanly.

DECLARE @strExecCommand varchar(255)
DECLARE @strLoginName varchar(30)
DECLARE @intReturnStatus integer

SELECT @strLoginName = 'jXrschenk'

---------------------------------------------------
-- Grant permissions to the designated Sql Login --
---------------------------------------------------

SELECT @strExecCommand

= 'GRANT SELECT, INSERT ON Songs TO '
+ @strLoginName

--------------------------------------------------------
-- Wrap the dynamically-constructed EXEC command in a --
-- temporary stored procedure so we have a chance to --
-- detect errors arising from it's execution. --
--------------------------------------------------------

EXEC (' CREATE PROCEDURE #GrantPermissions AS

DECLARE @intReturnValue integer

' + @strExecCommand + '

IF (@@ERROR = 0)
SELECT @intReturnValue = 0
ELSE
SELECT @intReturnValue = 1

RETURN (@intReturnValue)

') -- END EXEC

EXEC @intReturnStatus = #GrantPermissions

DROP PROCEDURE #GrantPermissions

SELECT GrantStatusCode = @intReturnStatus

------------------------------------------------------------------
-- 8. Errors in ancient command-line stuff (here in real world) --
------------------------------------------------------------------


------------------------------------------------------------------------------
-- Create a temporary table into which we will later EXEC command line data --
------------------------------------------------------------------------------

CREATE TABLE #CommandLineData (Returned_Data varchar(80) NULL)

INSERT #CommandLineData EXEC master..xp_cmdshell 'Dir C:\'

SELECT * FROM #CommandLineData

DROP TABLE #CommandLineData


-- Bringing it all together

sp_addmessage 50290, 10, 'usp_DropIndexes -- Success(%d): %s', NULL, TRUE, REPLACE
GO

sp_addmessage 50291, 10, 'usp_DropIndexes -- Failure(%d): %s', NULL, TRUE, REPLACE
GO

EXEC Songs.Corporate.usp_DropIndexes

@strTableName = 'Songs',
@strSearchIndex = '%banana', -- Search any index name
@intStatusInhibitMask = 6144, -- 6144 = Inhibit constraints
@blnDebugMode = 1



-- I CAN'T BELIEVE I ERROR-CHECKED THE WHOLE THING!

-- IF TIME PERMITS: Review the Drop Indexes Procedure

--Procedure: usp_DropIndexes
--Purpose: This procedure drops selected indexes from the named table

--ReturnStatus Codes:

-- 0 Successful completion
-- 1 Missing required table name
-- 2 Trouble encountered dropping index or constraint

-- Creation, Modification, Maintenance History
-------------- -------------- -------------------------------------------
-- DATE PROGRAMMER DESCRIPTION OF WORK ACCOMPLISHED
-------------- -------------- -------------------------------------------
-- 21-Oct-1999 Jeffrey Schenk Original creation of this procedure


CREATE PROCEDURE usp_DropIndexes

@strTableName varchar(30) = NULL, -- Dont actually let this fly
@strSearchIndex varchar(30) = '%', -- Search any index name
@intStatusInhibitMask integer = 0, -- 6144 = Inhibit constraints
@blnDebugMode bit = 0

AS

---------------------------------
-- Local variable declarations --
---------------------------------

DECLARE @messageText varchar(255)
DECLARE @strExecCommand varchar(255)
DECLARE @blnConstraint bit
DECLARE @intErrorBuffer integer
DECLARE @intReturnValue integer
DECLARE @intReturnStatus integer

DECLARE @intIndexStatus smallint
DECLARE @strIndexName varchar(30)

------------------------------
-- Initialization and Setup --
------------------------------

SELECT @intErrorBuffer = 0
SELECT @intReturnValue = 0
SELECT @messageText = ''


IF (@strTableName IS NOT NULL)
BEGIN

----------------------------------------------------------
-- Create a row cursor to iterating through each record --
----------------------------------------------------------

DECLARE csrSysIndexes CURSOR FOR

SELECT
name, status
FROM
dbo.sysindexes
WHERE
(ID = OBJECT_ID('Corporate.' + @strTableName))
AND (name LIKE @strSearchIndex)
AND ((status & @intStatusInhibitMask) = 0)
AND (indid BETWEEN 1 AND 254) -- Exclude Tables, TextPages

------------------------------------------------
-- Open the cursor for the selected recordset --
------------------------------------------------

OPEN csrSysIndexes

-------------------------------------------------------------
-- Go fetch the first record (to set the fetchstatus flag) --
-------------------------------------------------------------

FETCH NEXT FROM csrSysIndexes INTO @strIndexName, @intIndexStatus

------------------------------------------------------
-- While not stepped beyond the ends of the dataset --
------------------------------------------------------

WHILE (@@FETCH_STATUS <> -1)
BEGIN
---------------------------------------------------------------------
-- This record still a member of the resultset? (not deleted, etc) --
---------------------------------------------------------------------

IF (@@FETCH_STATUS <> -2)
BEGIN
--------------------------------------------------------------
-- Determine whether or not this index has been implemented --
-- as a constraint (2048 or 4096 bits set in status code). --
--------------------------------------------------------------

IF ((@intIndexStatus & 6144) > 0)
BEGIN
SELECT @blnConstraint = 1

SELECT @strExecCommand
= 'ALTER TABLE Corporate.'
+ @strTableName
+ ' DROP CONSTRAINT '
+ @strIndexName

END ---------------------------------------------------------
ELSE -- This index has not been implemented as a constraint --
BEGIN ---------------------------------------------------------

SELECT @blnConstraint = 0

SELECT @strExecCommand
= 'DROP INDEX Corporate.'
+ @strTableName
+ '.'
+ @strIndexName

END

IF(@blnDebugMode = 1)
RAISERROR(@strExecCommand, 10, 1)

----------------------------------------------------------
-- Kick this bird out of the nest, and see if it'll fly --
-- (or go drop the index or constraint now ;) --
----------------------------------------------------------

EXEC (@strExecCommand)

-----------------------------------------------------------------
-- Sometimes they don't fly... so scan the ground for wreckage --
-----------------------------------------------------------------

IF (@@ERROR = 0)
BEGIN
SELECT @messageText = @messageText + CHAR(13) + CHAR(10) + CHAR(9)
+ @strIndexName

END ---------------------------------------------------
ELSE -- Something went wrong, but we're clueless what --
BEGIN ---------------------------------------------------

SELECT @intReturnValue = (@intReturnValue | 2)

SELECT @messageText = @messageText + CHAR(13) + CHAR(10) + CHAR(9)
+ 'Errors: '
+ @strIndexName
END

END -- still within recordset bounds

FETCH NEXT FROM csrSysIndexes INTO @strIndexName, @intIndexStatus
END

CLOSE csrSysIndexes

DEALLOCATE csrSysIndexes

END ----------------------------------------
ELSE -- Errors detecting a valid tablename --
BEGIN ----------------------------------------

SELECT @intReturnValue = (@intReturnValue | 1)

SELECT @messageText = @messageText + CHAR(13) + CHAR(10) + CHAR(9)
+ 'Missing required table name'
END

----------------------------------------------------------------
-- Determine general success or failure of routine as a whole --
----------------------------------------------------------------

IF ((@@ERROR = 0) AND (@intReturnValue = 0))
BEGIN

RAISERROR(50290, -1, -1, @intReturnValue, @messageText)

END ---------------------------------------------------
ELSE -- Something went wrong, so expound a little bit --
BEGIN ---------------------------------------------------

RAISERROR(50291, -1, -1, @intReturnValue, @messageText)
END

RETURN(@intReturnValue)

GO