ENTER>
Writing a Stored Procedure
By Nathan Pond
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:
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. Later in 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. 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:
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 help 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
%>
|
Now, before I get to far, let me introduce the ALTER command you can use to overwrite
stored procedures, so you don't have to drop them then recreate them. You can use
the ALTER command like this:
ALTER PROCEDURE sp_myStoredProcedure
AS
......
Go
|
This will overwrite the stored procedure that was there with the new set of commands, but
will keep permissions, so it is better than dropping and recreating the procedure.
As promised I am going to dive into more detail about stored procedures. Let me start out
by answering a common question I received via e-mail. Many people wrote asking if it was
possible, and if so how to do it, to use stored procedures do to more than select
statements. Absolutely!!! Anything that you can accomplish in a sql statement can be
accomplished in a stored procedure, simply because a stored procedure can execute sql
statements. Let's look at a simple INSERT example.
CREATE PROCEDURE sp_myInsert
@FirstName varchar(20),
@LastName varchar(30)
As
INSERT INTO Names(FirstName, LastName)
values(@FirstName, @LastName)
Go
|
Now, call this procedure with the parameters and it will insert a new row into the
'Names' table with the 'FirstName' and 'LastName' columns
approiately assigned. And here is an example of how to call this procedure with parameters
from an ASP page:
>%
dim dataConn, sSql
dim FirstName, LastName
FirstName = "Nathan"
LastName = "Pond"
set dataConn = Server.CreateObject("ADODB.Connection")
dataConn.Open "DSN=webData;uid=user;pwd=password" 'make connection
sSql = "sp_myInsert '" & FirstName & "', '" & LastName & "'"
dataConn.Execute(sSql) 'execute sql call
%>
|
Remeber, you can use stored procedures f
-- or anything, including UPDATE and
DELETE calls. Just embed a sql statement into the procedure. Notice that
the above procedure doesn't return anything, so you don't need to set a recordset. The
same will be true for UPDATE and DELETE calls. The only
statement that returns a recordset is the SELECT statement.
Now, just because a recordset isn't returned, it doesn't mean that there won't be a return
value. Stored procedures have the ability to return single values, not just recordsets.
Let me show you a practical example of this. Suppose you have a login on your site, the
user enters a username and password, and you need to look these up in the database, if
they match, then you allow the user to logon, otherwise you redirect them to an incorrect
logon page. Without a stored procedures you would do 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 * From User_Table Where UserName = '" & _
Request.Form("UserName") & "' And Password = '" & _
Request.Form("Password") & "'"
Set rs = dataConn.Execute(sSql) 'execute sql call
If rs.EOF Then
'Redirect user, incorrect login
Response.Redirect "Incorrect.htm"
End If
'process logon code
.............
%>
|
Now let's look at how we would accomplish this same task using a stored procedure.
First let's write the procedure.
CREATE PROCEDURE sp_IsValidLogon
@UserName varchar(16),
@Password varchar(16)
As
if exists(Select * From User_Table
Where UserName = @UserName
And
Password = @Password)
return(1)
else
return(0)
Go
|
What this procedure does is take the username and password as input parameters and performs
the lookup. If a record is returned the stored procedure will return a single value of 1,
if not the procedure will return 0. No recordset is returned. Let's look at the asp you
would use:
>%
<--#INCLUDE VIRTUAL="/include/adovbs.inc"-->
dim dataConn, adocmd, IsValid
set dataConn = Server.CreateObject("ADODB.Connection")
dataConn.Open "DSN=webData;uid=user;pwd=password" 'make connection
Set adocmd = Server.CreateObject("ADODB.Command")
adocmd.CommandText = "sp_IsValidLogon"
adocmd.ActiveConnection = dataConn
adocmd.CommandType = adCmdStoredProc
adocmd.Parameters.Append adocmd.CreateParameter("return", _
adInteger, adParamReturnValue, 4)
adocmd.Parameters.Append adocmd.CreateParameter("username", _
adVarChar, adParamInput, 16, _
Request.Form("UserName"))
adocmd.Parameters.Append adocmd.CreateParameter("password", _
adVarChar, adParamInput, 16, _
Request.Form("Password"))
adocmd.Execute
IsValid = adocmd.Parameters("return").Value
If IsValid = 0 Then
'Redirect user, incorrect login
Response.Redirect "Incorrect.htm"
End If
'process logon code
.............
%>
|
Now lets slow down for a minute and I'll go through what we just did. First thing I did was create
a command object for ADO. I did this with:
|
Set adocmd = Server.CreateObject("ADODB.Command")
|
Next I had to tell the object what command it would be executing, with this line:
|
adocmd.CommandText = "sp_IsValidLogon"
|
Notice that the command is the name of the stored procedure.
You must tell the command object which connection (database) to use, to do this you use
.ActiveConnection. .CommandType is a property that tells sql
what type of command it is trying to execute. adCmdStoredProc is a constant
variable declared in the include file adovbs.inc.
(For more information on adovbs.inc, be sure to read
ADOVBS.INC - Use It!)
It represents the number telling sql that the command is to execute a stored procedure.
The .Append method is used to add return values and parameters. I had to add
the username and password parameters, as well as set up the return value. I then executed
the command with .Execute, and .Parameters("return").Value held
the return value from the procedure. I set that to the variable IsValid.
If IsValid is 0, the login is incorrect, if it is 1, the login is correct.
Now even after the explanation this is still a lot to take in. My recommendation to you
is to dive into your server and try a few simple tasks like this. Practice makes perfect.
One note: sometimes I get errors when I try to .Append the return value after
I have already set the parameters. Meaning I might get an error if the above code looked
like this:
>%
.....
Set adocmd = Server.CreateObject("ADODB.Command")
adocmd.CommandText = "sp_IsValidLogon"
adocmd.ActiveConnection = dataConn
adocmd.CommandType = adCmdStoredProc
adocmd.Parameters.Append adocmd.CreateParameter("username", _
adVarChar, adParamInput, 16, Request.Form("UserName"))
adocmd.Parameters.Append .CreateParameter("password", _
adVarChar, adParamInput, 16, Request.Form("Password"))
adocmd.Parameters.Append .CreateParameter("return", _
adInteger, adParamReturnValue, 4)
adocmd.Execute
IsValid = adocmd.Parameters("return").Value
.....
%>
|
I'm not exactly sure why this happens, but I just made it a habit to declare the return
value first, then the parameters.
Now I know what some of you are saying. "The original ASP example for checking the username
and password without using a stored procedure is so much easier, all you did was confuse
me! Can Stored Procedures actually be used to improve efficiency?" Well I'm glad you
asked, because although the example above did require a bit more code, it is important to
realize that it is much more efficient. Stored procedures have other benefits besides
efficiency, though. For a full explanation of the benefits of stored procedures, be sure
to read the SQL Guru's Advice on the issue.
And now I am going to show you an example of a task where using stored procedures minimizes
your database calls.
Assume you have the same script as before for validating usernames and passwords. All it
really does is say whether it is a valid username and password. Suppose you want to add
functionality in to log all failed attempts at logging on into another table called
'FailedLogons'. If you weren't using a stored procedure you would have to
make another call to the database from your ASP code. However, in the example using the
stored procedure, we don't have to touch the ASP code at all, we simply modify the
procedure like so:
ALTER PROCEDURE sp_IsValidLogon
@UserName varchar(16),
@Password varchar(16)
As
if exists(Select * From User_Table
Where UserName = @UserName
And
Password = @Password)
begin
return(1)
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@UserName, @Password)
return(0)
end
Go
|
Wasn't that neat? But that's not all, while we're at it why not add a little more
functionality? Let's say that we want to run a check on each incorrect login, and if
there have been more than 5 incorrect logins for that username within the past day, that
account will be disabled. We would have to have the 'FailedLogons' table set
up to have a 'dtFailed' column with a default value of ('GetDate()').
So when the incorrect logon is inserted into the table, the date and time is recorded
automatically. Then we would modify our stored procedure like this:
ALTER PROCEDURE sp_IsValidLogon
@UserName varchar(16),
@Password varchar(16)
As
if exists(Select * From User_Table
Where UserName = @UserName
And
Password = @Password
And
Active = 1)
begin
return(1)
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@UserName, @Password)
declare @totalFails int
Select @totalFails = Count(*) From FailedLogons
Where UserName = @UserName
And dtFailed > GetDate()-1
if (@totalFails > 5)
UPDATE User_Table Set Active = 0
Where UserName = @UserName
return(0)
end
Go
|
Now, let's take a closer look at what I was doing. First thing, check to see if the
username and password exist on the same row, and that that user is active, if so, login is fine, return 1 to the
user and exit the procedure. If the login is not ok though, we want to log it. The first
thing the procedure does is insert the record into the 'FailedLogons' table.
Next we declare a variable to hold the number of failed logons for that same day. Next we
assign that value by using a sql statement to retrieve the number of records for that username,
within the same day. If that number is greater than 5, it's likely someone is trying to
hack that account so the the username will be disabled by setting the active flag in the
'User_Table' to 0. Finally, return 0 letting the calling code (ASP) know that
the login was unsuccessful. To accomplish this same task using only ASP, you would have
needed to make 4 database calls. The way we just did it it is still only one database call,
plus the fact that all that functionality we added at the end was in the stored procedure,
we didn't have to touch the ASP code at all!
Note about 'begin/end': When using an 'If' statement in a stored procedure, as long
as you keep the conditional code to one line you won't need a 'begin' or
'end' statement. Example:
if (@myvar=1)
return(1)
else
return(2)
|
However, if you need more than one line, it is required that you use begin
and end. Example:
if (@myvar=1)
begin
do this.....
and this.....
return(1)
end
else
begin
do this....
return(2)
end
|
I hope that I have given enough information to keep you active in learning stored procedures.
If you're anything like me, getting the basics is the hard part, from there you can experiment
and learn on your own. That is why I decided to create this article. Feel
free to e-mail me at nathan@npond.com with any
questions or comments about any of my articles.
Happy Programing!
|
