Running the stored procedure: GetSalesById

Script Output:

Running the stored procedure: GetSalesById.

Passing it two parameters:
@Id = 1
@Sales = (an output parameter)

It returned the value: 5000.

 

Running the stored procedure: GetNameInfoById.

Passing it one parameter:
@Id = 1

It returned a recordset which I used to print out this name: Andrew Fuller.


Many ASP users start playing with a database by using Access. While this can be a good way to get you started, Access really isn't a great choice for your web app's back end. Unfortunately taking the leap into the world of SQL Server and its stored procedures can be quite daunting. Here's a sample to help out...

One of the performance increases you get from moving to SQL Server comes from the fact that you can use pre-compiled queries called stored procedures as opposed to dynamically generated SQL statements. Here are the two examples that are used by this sample. This first one takes one input parameter and returns one output parameter.

Create Procedure GetSalesById
    (
        @Id int,
        @Sales money OUTPUT
    )
As
    SELECT @Sales = sales
    FROM sample
    WHERE id = @Id

That's pretty simple, but what good would a SQL query be if it could only return a single value so I've also included a sample that will return a full data set. In this case it only returns one record (to keep the sample simple), but there's no reason it couldn't return more.

Create Procedure GetNameInfoById
    (
        @Id int
    )
As
    SELECT first_name, last_name
    FROM sample
    WHERE id = @Id

Now that you've seen the listings for the stored procedures take a look at the source to the ASP script to see how to call them from an ASP page (included in zip file).




About the Author

From ASP101

Articles originally posted on ASP101.com

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds