Create Stored Procedures – Your First Walk Through
I recently put together a lesson on how to create stored procedures. It is part of my Stored Procedures Unpacked course. When you create stored procedures, you can encapsulate logic securely, while allowing client program an easy way to execute it.
I put this walk through together so that you’ll see how the whole process works. I think, that before we dive too deeply into how to create stored procedures, it first makes sense to see the whole picture.
After watching this video, create stored procedures to practice. You find out isn’t as hard as everyone makes them out to be and you’ll have one of the skills a junior DBA should know.
You can learn more about this this video, it’s transcript follows:
Why do we Need to Create Stored Procedures??
What we’re going to do in this lesson is create a stored procedure, then execute that stored procedure, and then view the results, so don’t freak out if this seems like too much!
The idea here is just to show you the main steps of how to create a stored procedure.
Later on, we’ll go over all the details of what the stored procedure does, and the various aspects of the stored procedure, such as the parameters.
The stored procedure we’re going to create is one to calculate the time to travel a distance. So, the steps we’re going to take, we will accept the distance and velocity as parameters. And then, based on that, we will calculate the time as the distance divided by velocity, and then, we will return the result. So, let’s go ahead and do that.
Before we get started and create stored procedures, I want that talk about where stored procedure objects are located in Management Studio.
You’re familiar with where tables are located in the Tables area, but if you notice, you’re not going to see anything in here that talks about stored procedures. Where stored procedures are hiding is in this area called Programmability.
I click open Programmability, you’ll see that there are other folders here, and one is called Stored Procedure. And then, underneath the Stored Procedure folder will be all the stored procedures that have been created.
What I’m going to do now is open a new query window so that we can create our first stored procedure. I’ve created a query window that points to AdventureWorks database, and at this point, I’m going to bring in the command to create a stored procedure. To do that, it’s CREATE PROCEDURE and then AS and then BEGIN and then END.
This is the main bones or skeleton of the stored procedure, I have what the procedure is, its name, and then the BEGIN and END signal, the start and finish of the procedure, and any commands that belong within this stored procedure will reside within the BEGIN and END.
Before I get much further, I need to add parameters, and the parameters go between the CREATE PROCEDURE and the AS. Here what I will do is I’m going to put in my parameters, and as we talked about before, we have distance and velocity.
I’m bringing in the parameter for distance as a float, it’s going to have a decimal value and velocity. Notice that parameters have @ sign in front of it. This is the character you get when you hit Shift and 2, and now I’m ready to put in the code.
The first piece I’m going to put in is the part with the NOCOUNT. The NOCOUNT essentially is put in so that SQL doesn’t return extra results saying how many rows are returned in the last SELECT that was run.
And now, I will put in the statement for the velocity, so a couple things just to show here real quickly is I’m using some logic.
I’m going to test that if the velocity is not equal to zero, then let’s SELECT the expression, @distance / @velocity, so, again, this would be like the 120 miles divided by 60 miles per hour to give us the two hours that it took.
This is going to return the result. If my velocity equals zero, which you know from math you can’t divide something by zero, so we’re just going to return a zero.
This is my stored procedure. The stored procedure has a name. It has parameters. It has the NOCOUNT. This is standard, and then here’s the code that runs and executes. We will be returning the distance divided by velocity, so basically the time it takes to travel a distance based on how fast we’re going. A very simple example, but hopefully, it’s showing you how a stored procedure is built.
So, let me run this to create our stored procedure. ou can see it was successful. I’m going to come over to my database window, and I’m going to refresh it and come back into the Programmability, Stored Procedures.
And now, you can see there’s CalculateTime. It has parameters now, and it returns an integer. So, the database figured out what our procedure takes, and how it will tick.
Let’s run this stored procedure. I’ve already set up a example. How we can run our stored procedure is to create a new query window, and we can use the exec command. Exec is short for execute, and I can type in exec or execute.
And now, I’m running the stored procedure, and here, I’m saying, “We are going a distance of 120, and we’re going 40 miles per hour.” And when I run this, it should come back with the value of 3, and as you can see, it does come back with 3.
If I was to change our velocity to zero, what would happen? It comes back with zero because if you remember, we had that IF-THEN statement that said that if our velocity was zero, we can’t divide by zero, so return a zero. Again, if the amount of velocity is zero, if velocity is not equal to zero, do the calculation, otherwise, SELECT a zero.
Here, in a nutshell, is how you can create and run a stored procedure. We create a stored procedure using the CREATE PROCEDURE command, and then we can run it using execute.
Again, the whole idea here isn’t to teach you how to create the stored procedure and send you on your way and say, “All right. You’re good to go. Don’t take any more of the class.”
It’s just to show you here’s the beginning steps on how to create a stored procedure, let you get your feet wet, let you understand that there’s no secret magic behind this. You could go out right now into your database and create a very simple stored procedure to do a calculation to add a couple numbers together and prove to yourself that you can do it.
In fact, that would be a great exercise. Go out and just try a simple stored procedure to add two numbers together just so you know you can do it.