Writing a Stored Procedure

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:

CREATE PROCEDURE sp_myStoredProcedure AS Select column1, column2 From Table1 Go

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 Where column1 = 0

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:

CREATE PROCEDURE sp_myStoredProcedure @myInput int AS Select column1, column2 From Table1 Where column1 = @myInput Go

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:

CREATE PROCEDURE sp_myStoredProcedure @myInput int, @myString varchar(100), @myFloat AS ..... Go

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:

<% dim dataConn, sSql, rs set dataConn = Server.CreateObject("ADODB.Connection") dataConn.Open "DSN=webData;uid=user;pwd=password" 'make connection sSql = "Select column1, column2 From Table1" Set rs = dataConn.Execute(sSql) 'execute sql call %>

Now let's see how we call the stored procedure.

<% dim dataConn, sSql, rs set dataConn = Server.CreateObject("ADODB.Connection") dataConn.Open "DSN=webData;uid=user;pwd=password" 'make connection sSql = "sp_myStoredProcedure" Set rs = dataConn.Execute(sSql) 'execute sql call %>

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:

<% dim dataConn, sSql, rs, myInt myInt = 1 'set myInt to the number we want to pass to the stored procedure set dataConn = Server.CreateObject("ADODB.Connection") dataConn.Open "DSN=webData;uid=user;pwd=password" 'make connection sSql = "sp_myStoredProcedure " & myInt Set rs = dataConn.Execute(sSql) 'execute sql call %>

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!