AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=408&pId=-1
Stored Procedures Quickstart
page
by Chris Garrett
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 16371/ 39

Stored Procedures Quickstart

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.


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-21 12:33:24 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search