Eventually, most ASP programmers will find they need to write stored procedures because they find they are writing the same or similar SQL queries over and over in their web project or will require the performance that only a stored procedure will bring.
Other reasons for using Sprocs is they simplify security settings and aid in the development to an N-Tier architecture, helping to separate out data-access code.
Some people are put off Sprocs because they perceive them to be difficult; this couldn't be further from the truth, as I will show in this article.
What are Stored Procedures?
If you think of Sprocs as being sql scripts you won't be far wrong. They are bundles of SQL commands stored in the database but with the added advantage that once it has been run it is compiled and cached, future calls to the Sproc will be much faster.
First Stored Procedure
CREATE PROCEDURE listArticles
AS
SELECT *
FROM tblArticles
ORDER BY dtmDateStamp DESC
This stored procedure is a simple vanilla sql query, but demonstrates how to create a procedure. To test the procedure (assuming you have a tblArticles table!) you would simply enter the name of the procedure (“listArticles”) in SQL Query Analyser.
In ASP we can initially cut out the SQL query we have been using and put the procedure name in its place, eg.
'Create an ADODB Connection
set myConnection = server.createObject("ADODB.Connection")
myConnection.open "myDSN"
'Create a recordset
set myRecordSet = server.createObject("ADODB.Recordset")
'Fill recordset with records from database
myRecordSet.open "listArticles", myConnection, 2 ,3
'Read records
do while not myRecordSet.EOF
response.write(myRecordSet("strTitle"))
myRecordSet.movenext
loop
'Close connections
myRecordSet.Close
myConnection.Close
This is fine, and it works, but when you are comfortable with the SQL side you might want to look at the Execute command. I find the above code easier to understand for people new to the subject.
