Stored Procedures Quickstart
page 1 of 1
Published: 25 Mar 2004
Unedited - Community Contributed
Abstract
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.
by Chris Garrett
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 21879/ 38

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.


User Comments

Title: sproc   
Name: sandeep kamboj
Date: 2009-02-24 8:55:35 AM
Comment:
i want more explnation like i/o procedure etc
Title: SPROC   
Name: Logesh
Date: 2008-03-18 3:23:27 AM
Comment:
This article looks good,But needs more explanation.
Title: SPROC   
Name: Praveen Kumar
Date: 2007-07-10 3:25:55 AM
Comment:
This article is good but needs more explanation for SPROCS....to understand in a better manner
Title: SPROC   
Name: Nitin Sharma (.Net Technologies)
Date: 2007-07-03 7:58:44 AM
Comment:
It is not at all Explanatory.....
Be very precise....about advantages and disadvantages of SPROC...

Thanks
Nitin Sharma
Title: Please Make it Sufficent   
Name: Suresh Kajla
Date: 2007-04-13 7:30:57 AM
Comment:
hi.
well the material is good but not satisfying .. we need more to know how it works... i have tried to get the master data base by this method but it was giving error that ordinal not found or does not exists , but i know that it exists and i just cant debugg the error ....... so it requires more explaination for this topic about various erros that we counter n how to over come them
Title: Its Useful for Begineers to Know about Sprocs   
Name: rajabide4u
Date: 2007-03-01 1:20:43 AM
Comment:
Its Useful to Know about Sprocs in easy manner.
Title: Stored Procedure   
Name: free things
Date: 2006-09-08 7:57:21 PM
Comment:
mySQL stored procedure actually work slower then regular function. MS SQL Stored procedure is better.
Title: IT WAS NOT SUFFUCIENT   
Name: Ram
Date: 2006-07-05 7:18:06 AM
Comment:
hi.
well the material is good but not satisfying .. we need more to know how it works... i have tried to get the master data base by this method but it was giving error that ordinal not found or does not exists , but i know that it exists and i just cant debugg the error ....... so it requires more explaination for this topic about various erros that we counter n how to over come them

than
ram
Title: doubt on stored procedure   
Name: siva
Date: 2006-05-04 8:06:56 AM
Comment:
Hi!That stuff was g8... May i know wat that "strtitle" mentions...is it coloumn name r ? ? ? ?Send me the reply 2 siva_ganeshk@yahoo.co.in
Title: Article Feedback : SProcs-II   
Name: Vikas Garg
Date: 2005-12-22 9:43:41 AM
Comment:
Surely,
You have best stuff for developers. But Please Update this site.

Thanks!!
ViG
vikas_gurukul@yahoo.com
Title: i want inset by php with database access   
Name: mohammed
Date: 2005-09-03 7:13:29 AM
Comment:
i want inset,insert ,update by php with database access
but i use php with mysql
Title: Feedback on SP   
Name: idiotb4u
Date: 2005-06-13 12:53:35 AM
Comment:
It's quite clearly about store procedure, and how to create it. But you haven't told me how to call it from an ASP page, or where can I find out more...
Please give me an sample!
Thanks
Title: Founder   
Name: billmiller@netscape.com
Date: 2005-04-24 3:30:43 AM
Comment:
Dear Chris,
This is off-point, but would you kindly comment on this problem:
I download and edit text myself on the above page, then upload. In the process I seem to undo my interface with PayPal as evidenced by the following error message:


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ucase(fldLoginName)='TESTTEST' and fldLenderId <>'.
/activate.asp, line 664

Thanks for any help!
Bill Miller
Title: feedback on stored Sprocs tutorial   
Name: ublend
Date: 2005-04-15 2:40:55 PM
Comment:
Good overview - ready to create and use one already!
Title: FeedBack   
Name: Vasu
Date: 2005-03-04 9:08:11 AM
Comment:
Hi Das,

This artile looks good, It gives overall idea about sprocs.

Thanks
Vasu V
Title: More than feedback   
Name: Brian
Date: 2005-02-21 11:37:21 AM
Comment:
Some of us are new newbies, if ya know what I mean. Is it possible that you or somebody could publish an article that explains the ASP call to stored procedures from start to finish like you were explaining it to a four year old? Some of us are a little slow, so for those of you who aren't, please have patience.
Title: Article Feedback : SProcs   
Name: Vikas Garg
Date: 2005-02-18 7:00:04 AM
Comment:
aha! nice one, but wouldnt it seems to be incomplete.
thx!
vikas garg
chandigarh
india
Title: feedback on stored procedures   
Name: jaishankar
Date: 2004-09-16 7:13:56 AM
Comment:
Hi,
This artile looks good,but would be more usefull if you provide more information ,a complete one.

Thanks,
jaishankar






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-07-26 8:45:42 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search