Making Stored Procedures Bulletproof

So last time in Stored Procedures – Why we talked briefly about ways of making stored procedures more bulletproof.

First off, you have to ask yourself, should we? I mean, the data we work with is a known thing every time, right? Can people just blindly enter data in? Haven’t our apps been built to stop stupidity?

For most of these, the answer is a definite maybe.

For example, recently, my wife worked with a business unit that did not expect a part to cost more than $9,999 for a single line item. When one did, it completely failed to process. Not only did it happen once, but 3 times in the same week for multiple clients!

And let’s not forget the entire Y2K issue where thousands of coders had written code as “efficiently” as possible, except for the fact that once it became the 21st century, the computer programs couldn’t determine whether it was dealing with 1900 or 2000!

One of the many ways to make your stored procedures more bulletproof is to raise an error. One of the ways we’ll talk about doing this is with the simple RAISERROR command – yes, that’s right. There really is only one E in that command name, while if this was 2 words, there would be 2 e’s. (To find out why there is only one E, go here. TL;DR version – Sybase.)

First off, we see our old code as:

CREATE PROCEDURE dbo.DivideBy (
	@Numerator   int,
	@Denominator int )
AS

SELECT @Numerator / @Denominator

Now, I know one of the many ways that this process could fail is because the Denominator is 0. If you try the process as it stands now with @Denominator = 0, you’ll get the following:

Now, with my tweak, I get the much more helpful:

Now, you’ll note that the error message is a bit more helpful now. At least, it can tell the user who to see about the issue. If you don’t know Julio – you obviously need some musical help from Paul Simon, Big Daddy Kane, Biz Markie, and a few other sports heroes.

To get this, I just changed my stored procedure to:

CREATE OR ALTER PROCEDURE dbo.DivideBy (
	@Numerator   int,
	@Denominator int )
AS

IF @Denominator = 0
BEGIN
	RAISERROR ('Nope. The Denominator cannot be 0. Go see Julio down by the school yard', 16, 0)
	RETURN
END

SELECT @Numerator / @Denominator

Yes, the RETURN is important. If you don’t put that after the RAISERROR statement, the process will continue on as if nothing happened and still error out – the exact thing you were trying to prevent!

Also, notice that I can put pretty much whatever I want within the single quotes – just try to be appropriate and helpful! It really is this simple to write.

Now, next time, I’ll show another way to make your stored procedures bulletproof and stop your stored procedures from stopping whenever there are differences between the data you expect and the data you get!