This article is a continuation of my previous article,
Writing a Stored Procedure
Let me start out by first correcting (or rather updating) something I said in my first
article. I said there that I wasn't aware of a way to update a stored procedure without
deleting it and recreating it. Well now I am. :-) There is an ALTER comand
you can use, 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.
Many thanks to Pedro Vera-Perez for e-mailing me with this info.
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 for 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
.............
%>
|
In Part 2 we'll look at the ADO Command
Object, and how you can use it to execute stored procedures through your ASP pages. We'll
also look at why you should use stored procedures as opposed to dynamic queries.
