-------------------------------------------------- -- SQL Error Handling Primer -- Jeffrey Schenk -- -------------------------------------------------- -- Primitive format to support pasting into Query Analyzer... --------------------------------------------------------------------- -- 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