|
|
|
|
home > code > tutorials > Fun With Stored Procedures… |
|
Fun With Stored Procedures…Consider this stored procedures 101... A brief and very useful tutorial on creating and using stored procedures with your asp applications. What is a stored procedure?A stored procedure is nothing more than an sql statement stored inside a database. The database can be SQL Server or MS Access as well as others ... A stored procedure is compiled by your database (for the most part) one time, when it is entered. This results in faster database executions and overall performance updates... as an added bonus, it further separates the sql statement from your asp leaving you with more readable code. So let's get on with it. Writing stored proceduresStored procedures are written using the structured query language (sql). The most important part of writing a stored procedure is the CREATE PROCEDURE statement. Without going into all the optional parts of the create procedure statement, here it is: CREATE PROCEDURE [sp_]ProcedureName ( parameters ) AS sql statement - can be INSERT, DELETE, SELECT, UPDATE... Well that's pretty worthless... Let's try a different approach. Assume you are working with the MyData.mdb database, the ASP Emporium test database, and you wanted to select all the new examples from the database. Your standard SQL string would probably look like this: SELECT * FROM examples WHERE new = 1; You've probably written that kind of statement a thousand times. Now let's take that sql statement and turn it into a stored procedure. CREATE PROCEDURE sp_GetNewExamples AS SELECT * FROM examples WHERE new = 1 And that's it, but your job is only 34% done. There are still two more steps to go. Getting the stored procedure into the database.There are a few ways to get a stored procedure into your database. The way I will show you will work for MS Access or SQL Server and should be pretty familiar. For this we need to switch over to ASP. You will need to create a file that will serve as the sql server/access procedure upload page. To solve this we'll use a cheesy app consisting of a real big form input and a simple routine to access the db and add our procedure. Source code for "add.asp" - cheesy app to add procedures to the database
<% @ Language = vbscript %>
<%
Option Explicit
Response.Buffer = True
Private Sub SQLExec(byVal sql)
dim c
set c = server.createobject("adodb.connection")
c.Open Application("dbConn")
c.Execute sql
c.Close
set c = Nothing
Response.Write "<h1><big>PROCEDURE ADDED!</big></h1>"
End Sub
dim tmp : tmp = Request.Form("SQL")
If left( ucase( trim( tmp ) ), 16 ) = "CREATE PROCEDURE" OR _
left( ucase( trim( tmp ) ), 14 ) = "DROP PROCEDURE" Then
Call SQLExec( trim( tmp ) )
Else
%>
<FORM ACTION="./add.asp" METHOD=POST>
<INPUT TYPE=SUBMIT VALUE="Add Procedure"><BR><BR>
<TEXTAREA NAME="SQL" COLS=65 ROWS=45></TEXTAREA><BR><BR>
<INPUT TYPE=SUBMIT VALUE="Add Procedure"></FORM>
<%
End If
%>
To use this app, just name it "add.asp" and put it somewhere in your server's public space (it wouldn't hurt to add some password protection). Access the page via the web and enter our new stored procedure into the form input like so: Click the submit button and the procedure is magically added to the database! Calling stored procedures in ASP pagesSELECT * FROM examples WHERE new = 1; Remember this? The original sql statement. It has now been rendered obsolete but we need to tell the database to use the procedure instead of the old sql statement. Assume we expanded on the original sql statement and the rest of the asp document looked like this:
<% @ Language = vbscript %>
<%
Option Explicit
Response.Buffer = True
dim c, r, sql
' This Is The OLD SQL statement to be replaced
sql = "SELECT * FROM examples WHERE new = 1;"
set c = server.createobject("adodb.connection")
c.Open Application("dbConn")
set r = c.Execute(sql)
do while not r.bof and not r.eof
response.write r("exampleName") & "<BR>"
r.movenext
loop
r.close
c.Close
set r = Nothing
set c = Nothing
%>
Replacing the old statement is real easy. You know the name of the stored procedure: sp_GetNewExamples so all you need to do is put the sql server/access keyword EXECUTE before the stored procedure's name: EXECUTE sp_GetNewExamples If we zoom out, the new sql string looks like this:
<% @ Language = vbscript %>
<%
Option Explicit
Response.Buffer = True
dim c, r, sql
' This Is The NEW SQL statement that calls
' a Stored Procedure .....
sql = "EXECUTE sp_GetNewExamples"
set c = server.createobject("adodb.connection")
c.Open Application("dbConn")
set r = c.Execute(sql)
do while not r.bof and not r.eof
response.write r("exampleName") & "<BR>"
r.movenext
loop
r.close
c.Close
set r = Nothing
set c = Nothing
%>
And all done. The procedure is added and being used. Well, that's great but how do you create a complex procedure that is reusable and has different input every time it runs... More Complex Stored ProceduresAssume we have a new sql statement:
SELECT
*
FROM
examples
WHERE
exampleName LIKE '%" & Request.Form("frmName") & "%'
OR
exampleDesc LIKE '%" & Request.Form("frmName") & "%'
ORDER BY
exampleName;
In this statement we create an sql string based on the form input "frmName" which is used to search two fields in the database. To create a stored procedure based on this new sql string, we will need to create a parameter in our create procedure statement. A parameter is a variable or group of variables that sql server/access will use to accept data from outside sources. The best way to see it is to look at the create procedure statement: You notice the parameters directly following the procedure name. Since we all want to be good programmers, we will write all of our procedure statements to work on sql server (they will also work on access as well..). ALL PROCEDURE VARIABLES MUST START WITH @. Access doesn't care but SQL server is very picky so why not do it right and use the @. Directly after the name of the variable comes the datatype of the field. We will be using SQL server datatypes because Access will convert them to access equivalents but sql server will not convert from access formats. This chart can serve as a guideline:
Parameters are separated from the rest of the create procedure statement with parenthesis and multiple parameters are separated by commas After we add our new procedure to the database, we need to replace our old sql statement with the new call to the stored procedure: Old SQL Statement:
SELECT
*
FROM
examples
WHERE
exampleName LIKE '%" & Request.Form("frmName") & "%'
OR
exampleDesc LIKE '%" & Request.Form("frmName") & "%'
ORDER BY
exampleName;
New SQL Statement:
This can be written a couple of different ways:
1.) with sql procedure variable names:
"EXECUTE sp_SrchNameDesc @frmInput1='%" & Request.Form("frmName") & _
"%', @frmInput2='%" & Request.Form("frmName") & "%'"
2.) without procedure variable names:
"EXECUTE sp_SrchNameDesc '%" & Request.Form("frmName") & "%', '%" & _
Request.Form("frmName") & "%'"
note: if you do not specify sql variable names, you must enter the input
in the same order as specified in the procedure.
Modifying and removing stored proceduresThe DROP PROCEDURE statement must be used to remove a stored procedure. If you wish to update a procedure, you must first drop it and reload a new procedure of the same name. DROP PROCEDURE ProcedureName And voila... Homemade stored procedures straight from the oven and into your code library. Now you can use stored procedures for all of your sql statements.... This is just the tip of the iceberg... Download |
