Browse DevX
Sign up for e-mail newsletters from DevX


Get Your Stored Procedures Ready for Anything-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Managing Errors Without Raiserror
Working from the command line is as close as you can get to imitating the facilities available from Raiserror. You can get most of Raiserror's benefit (error messages sent to the client) by executing your stored procedure from the command line by invoking OSQL. It will let you create a log file that can catch the server error messages. Other, more elegant approaches are possible using WSH or VB, but do you have the time to build an application to test an application? I didn't, so I split the difference by stubbing an error-trapping device at the end of the procedure. I then could get some insight into what went wrong, assuming that what did go wrong allowed the procedure to actually complete. If the error caused the procedure to end abnormally, I was out of luck because the error-trapping code would never be reached if the batch broke.

The following code is an error trap to test for parameter errors:

BEGIN --PARAMETER ERRORS-------------------------------------------------------- DECLARE @Errcode Int --sql error codes for transaction management DECLARE @InAction_Error INT, --custom error codes @EntryMonth_Error INT, @EntryYear_Error INT, @FileType_Error INT, @NewReins_Error INT, @Disenroll_Error INT, @Existing_Error INT --ERROR CODES------------------------------------------------------------- IF @InAction IS NULL BEGIN SET @InAction_Error = -10 GOTO ERRORH END IF @EntryMonth IS NULL BEGIN SET @EntryMonth_Error = -20 GOTO ERRORH END IF @EntryYear IS NULL BEGIN SET @EntryYear_Error = -30 GOTO ERRORH END IF @FileType IS NULL BEGIN SET @FileType_Error = -40 GOTO ERRORH END

The branching provided from this code put the procedure into a custom messages section of the code, where the message was displayed in the development environment. A custom message gives some inkling to the user of what was wrong with the parameter, and how to fix it so the procedure could run.

The following code represents some additional overhead in that you need to install the trapping above and create the GOTO Label section given below, but without it, you are in the dark as to what went wrong:

ERRORH: IF @InAction_Error <> 0 BEGIN IF @InAction_Error = -10 BEGIN Print 'ERROR -10 - Please set the first parameter to '+ char(10) + '''NEWREINS'' for New Reinstate, ' + char(10) + '''NEWREINSSNP'' for New Reinstate with SNP, '+ char(10) + '''DISENROL'' for Disenroll or ' + char(10) + '''EXISTING'' for Existing' END END IF @EntryMonth_Error<> 0 BEGIN IF @EntryMonth_Error = -20 BEGIN Print 'ERROR -20 - Please set the second parameter to '+ char(10) + 'an integer that stands for the Entry Month' END END IF @EntryYear_Error <> 0 BEGIN IF @EntryYear_Error = -30 BEGIN Print 'ERROR -30 - Please set the third parameter to '+ char(10) + 'an integer that stands for the Entry Year' END END IF @FileType_Error <> 0 BEGIN IF @FileType_Error = -40 BEGIN Print 'ERROR -40 - Please set the fourth parameter to '+ char(10) + '''A'', ''S'' or ''P'' for the File Type you are using ' END END GO

The native messages are no help, so this code lets you help yourself. It branches based on which parameter was insufficient so that the user (you) knows what to supply to the procedure on the next run.

In an effort to nip bugs in the bud, you should subject your code to boundary conditions, things that you might not have fully considered during development. For example, since the underlying tables might use characters instead of datetime datatypes, the incoming parameters are of type INT. This means you can't use date logic to test them.

For dates, in addition to the anticipated date parts of month (1-12) and year (1999-2004), use month zero, month 13, and month 100. To test your year handling, use year 0, 1,1900 and 10000. In addition to having an @@Error trap for the error codes returned by the database, you can get in front of the error by testing the values of the input parameters before execution. This way, you can handle the consequences of invidious input before it does any damage. Another benefit is that this approach informs the client of an appropriate list of values to get the procedure to work as anticipated.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date