Home arrow ASP arrow Page 2 - Pass That Parameter Over Here!
ASP

Pass That Parameter Over Here!


Stored procedures are a feature of popular database management systems such as Oracle and Microsoft SQL Server 2000. This article will give you a quick overview of stored procedures and how to pass parameters to them from ASP.

Author Info:
By: Mitchell Harper
Rating: 5 stars5 stars5 stars5 stars5 stars / 18
December 02, 2002
TABLE OF CONTENTS:
  1. · Pass That Parameter Over Here!
  2. · Creating the stored procedure
  3. · Executing our stored procedure
  4. · Conclusion

print this article
SEARCH DEVARTICLES

Pass That Parameter Over Here! - Creating the stored procedure
(Page 2 of 4 )

As mentioned above, we will be using ASP and ADO to access SQL Server 2000 and execute a stored procedure using parameters. Lets start by creating our own stored procedure. To save development time, we will create our stored procedure to work with the “Pubs” database that comes as part of every standard SQL Server 2000 installation.

Jump over to your database server, fire up Query Analyser (Start -> Programs -> Microsoft SQL Server -> Query Analyser) and login to your database with administrative rights. If you haven’t changed any settings, the default username of “sa” with no password should be fine.

Our stored procedure will interface with the authors table of the pubs database. It will return the number of authors who live in a specific state. Our input parameter (stateName) will hold the value of the state that we want to search on. Our output parameter (numResults) will return the number of authors that live in the state that we have specified. Enter the following code into the Query Analyser window (Ignoring the line numbers):

1. USE Pubs

2. GO

3.

4. CREATE PROCEDURE sp_GetAuthorCountByState

5. @stateName [CHAR](2),

6. @numResults [INT] OUTPUT

7.

8. AS

9. SELECT @numResults =

10. ( SELECT COUNT(*)

11. FROM authors

12. WHERE state = @stateName

13. )

14. GO


The QueryAnalyser window with our stored procedure code

For those of you who are just starting to learn SQL, or haven’t worked with parameterised stored procedures before, the code above may look a little challenging. As usual, I’ll walk you through the code in a line-by-line format.

On line one, we’re letting SQL server know that all of the commands we’re about to issue should be executed against the Pubs database. If we left line one out, all of our commands would be executed against the master database (not a good idea). The “GO” command on line two tells SQL Server to “execute the batch of commands up until this line but don’t go any further”, meaning that only our “USE Pubs” command would be executed (Switching the selected database from master to pubs).

Lines four handles the declaration of our stored procedure. Our stored procedure will be named sp_GetAuthorCountByState. When you’re creating stored procedures it’s a good idea to prefix them with “sp_” or something similar because:

1. They can be easily identified as stored procedures

2. It helps maintain good coding habits.

On line 5 we declare out input parameter. We’re telling SQL server that we will be passing it a variable named stateName. StateName will be of type character and will be two bytes long.

On line 6 we declare our output parameter that will be used to store the number of results matching our query. The parameter will be named numResults and will be of type integer. Notice the special “OUTPUT” declaration tacked onto the end? This just lets SQL Server know that the numResults variable needs to be returned to whichever program/script called the stored procedure.

Line eight starts the body of our stored procedure and lines nine to thirteen use the SQL COUNT() function to set the output parameter (numResults) to the number of rows in the authors table whose state field matches the value of our input parameter (stateName). There are two important points to note here:

1. All variables are prefixed with the @ symbol

2. When the value of a variable is changed, we use the SELECT command to set the new value.

To save our stored procedure under the Pubs database, press F5 or click the play button on the toolbar. Query Analyser should respond with “The command(s) completed successfully.”, meaning that our stored procedure was created without any trouble. You can make sure your stored procedure was correctly registered by using Enterprise Manager. Drill down to the Stored Procedures node of the Pubs database and you should see something like this:

Our newly created procedure is now part of the Pub

Now that we’ve created our stored procedure, we can use ASP and ADO to execute it.
blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials