Stored Procedures – Why

So, last time we discussed why do we really want to use a stored procedure. One of the big things I said was, “so that my blocks of code can be re-used several times now and in the future. “

This statement, at least to me, forms the building block for stored procedures. They allow me to use the same generic “blocks” of code and change only small portions of it and get the answers I need!

So, let’s create a very simple example.

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

SELECT @Numerator / @Denominator

With what we learned last time, this should look fairly familiar. A simple CREATE PROCEDURE. A simple name. But what are these things between the parentheses? Those are our friends and yours – parameters. They are what lets us do different things with our procedures and be smart about them.

So, the @Numerator parameter we have declared as an integer. Also, notice the ‘@’ in front of the word. It’s there to tell SQL that it’s a variable – which means that it can be almost anything – well, in this case, any integer anyway.

So, I’m going to run our little procedure with parameters as follows:

EXEC dbo.DivideBy  18.6, 7.2

Very simple. Very straight forward.

When it’s run, I get the answer: 2.

Not 2.something. Just 2.

Wait. That can’t be right.

Well, my friends, that’s because SQL Server sees 18.6 as an integer – so 18. It sees 7.2 as an integer – so 7. And we all know that 18/7 = 2 and a little bit of change. Does SQL Server let you know about any of that? Nope. It just says 2.

I bring this up so that as you do math with SQL Server, you can be aware that it only does exactly what you tell it to do. So, to fix our stored procedure, we can do the following:

DROP PROCEDURE IF EXISTS dbo.DivideBy
GO

CREATE PROCEDURE dbo.DivideBy (
	@Numerator decimal(8, 2),
	@Denominator decimal(8, 2) )
AS

SELECT @Numerator / @Denominator

Now, when we run the same example as before – EXEC dbo.DivideBy 18.6, 7.2 – we get 2.583333333333. Way better than 2.

Now, next time, we’ll delve into some other things to make our stored procedures smarter and more “bullet-proof!”