Writing a Stored Procedure
By Nathan Pond
Once You've Finished Reading... |
---|
Once you've completed this article be sure to check out Nathan Pond's follow-up article: Writing a Stored Procedure, Part II! |
If you're anything like me, you don't easily pick up on development techniques just by hearing about them. When I first installed my MS SQL server on my computer, it opened up a whole new world of features that I had never used. Among these were Stored Procedures. This article is designed to tell you how to begin writing stored procedures. I am using Microsoft SQL Server 7.0, but these examples should work in any SQL version.
Writing Stored Procedures doesn't have to be hard. When I first dove into the technology, I went to every newsgroup, web board, and IRC channel that I knew looking for answers. Through all the complicated examples I found in web tutorials, it was a week before I finally got a working stored procedure. I'll stop rambling now and show you what I mean:
Normally, you would call a database with a query like:
Select column1, column2 From Table1
|
To make this into a stored procedure, you simple execute this code:
|
That's it, now all you have to do to get the recordset returned to you is execute the stored procedure. You can simply call it by name like this:
sp_myStoredProcedure
|
Note: You can name a stored procedure anything you want, provided that a stored
procedure with that name doesn't already exist. Names do not nee to be prefixed with
sp_
but that is something I choose to do just as a naming convention. It is also
somewhat a standard in the business world to use it, but SQL server does not require
it.
Now, I realize you aren't gaining much in this example. I tried to make it simple to make it easy to understand. In part II of this article, we'll look at how it can be useful, for now let's look at how you can call a Stored Procedure with parameters.
Let's say that we want to expand on our previous query and add a WHERE
clause.
So we would have:
Select column1, column2 From Table1
|
Well, I know we could hard code the Where column1 = 0
into the previous stored
procedure. But wouldn't it be neat if the number that 0 represents could be passed
in as an input parameter? That way it wouldn't have to be 0, it could be 1, 2, 3,
4, etc. and you wouldn't have to change the stored procedure. Let's start out by
deleting the stored procedure we already created. Don't worry, we'll recreate it
with the added feature of an input parameter. There isn't a way that I'm aware of
to simply over-write a stored procedure. You must drop the current one and
re-create it with the changes. We will drop it like this:
DROP PROCEDURE sp_myStoredProcedure
|
Now we can recreate it with the input parameter built in:
|
Ok, why don't we pause here and I'll explain in more detail what is going on. First off, the parameter: you can have as many parameters as you want, or none at all. Parameters are set when the stored procedure is called, and the stored procedure receives it into a variable. @myInput is a variable. All variables in a stored procedure have a @ symbol preceding it. A name preceded with @@ are global variables. Other than that, you can name a variable anything you want. When you declare a variable, you must specify its datatype. In this case the datatype is of type Int (Integer). Now, before I forget, here's how to call the stored procedure with a parameter:
sp_myStoredProcedure 0
|
If you want more than one parameter, you seperate them with commas in both the stored procedure and the procedure call. Like so:
|
And you would call it like this:
sp_myStoredProcedure 0, 'This is my string', 3.45
|
Note: The varchar datatype is used to hold strings. You must specify the length of the string when you declare it. In this case, the variable is assigned to allow for 100 characters to be held in it.
Now, I'm sure some of you are wondering if there is a difference for SQL calls coming from ASP. There really isn't, let's take our first stored procedure example and I'll show how it is called from ASP. If it wasn't a stored procedure, you would call it something like this:
<%
|
Now let's see how we call the stored procedure.
<%
|
As you can see, the only difference is the query that is to be executed, which is stored in the sSql command. Instead of being the actual query, it is simply the name of the stored procedure. Now let's take a quick look at how you would call it with parameters. In our second example, we created the stored procedure to accept one integer parameter. Here's the code:
<%
|
Well, that's all for this article. Sometime in the near future I plan on writing a second part that really dives into more specifics about stored procedures. I hope this is enough to get you started though. Feel free to e-mail me with any questions or comments about the article!
Happy Programming!