Some Background on Stored Procedures

Introduction. A stored procedure is a collection of precompiled SQL commands that are directly accessible for use within a DBMS. Typically, they are used when certain operations will be frequently repeated. In these sorts of situations, rather than rewriting and/or compiling particular code it is worthwhile to have it stored in a precompiled format that is ready to run.

It should also make sense that it is desirable to be able to pass at least some parameters into stored procedures before they execute. This will greatly enhance their usability and adaptability to certain situations.

For example, maybe you are running reports that sample data between a start and end date. Rather than writing SQL code for each implementation you can make use of a stored procedure written by someone else. This will work even better if there is some sort of GUI where a potential user can select the start and end date from appropriate form controls and then press a button to retrieve the appropriate report. If the stored procedure has a configuration that accepts these start and end dates as inputs then there will need to be much less code rewriting. Obviously, functionality can be improved even more if the user has a fairly friendly GUI environment where they can choose other important parameters.

Web Database Applications. For web applications that rely on databases for data operations, stored procedures can significantly improve function. But first I want to present a bit of jargon associated with these sorts of applications.

A two-tiered web database application is based on a traditional client-server interaction. There is a front end interface that resides on the client or is downloaded to the client. Based on user interactions via this interface data is then retrieved from a DBMS on a server. The following diagram represents this situation.

In web applications this implies that the web server and database server are the same machine.

Three tiered web database applications have a database server separate from the web server. It is most typical to have the web server as the intermediary between the client and the database. This is illustrated in the next diagram.

This approach has a number of advantages.

the web server can act as a security buffer to the database server

database servers typically contain more sensitive information

the database server can actually be significantly deeper within the trusted administrative domain

it might not even have a public IP address that outside users can discover

the database server can focus on database operations and be accessible from several other servers and clients

the web server can focus on providing web service

In this sort of setting, with two and three tiered web database applications running in a Microsoft environment, the web server running IIS can

receive the requests for a webpage from a client

process any HTML form inputs

process the ASP requests

process the database requests obtaining user inputs and submit them to the database server

The database server, likely running SQL Server can

run stored procedures based on the call and passed parameters from an ASP

return the results of this processing into a recordset or some other view to the web server for further processing

This sort of approach, making use of ASPs as middleware ensuring coordination of results with client requests is quite prevalent on the Internet. It also keeps packet sizes and network traffic volume much smaller than it would be in most other configurations.

You also see analogous approaches being implemented with things like JSP or PHP as the middleware using different web server and database server software.

More on Stored Procedures. Now we need to get back discussing and developing stored procedures. Stored procedures are both precompiled and running quite close to the machine in order to enhance the effectiveness. This may sound like anti-Microsoft philosophy. But Microsoft does have some capacity to adapt to different user situations.

Stored procedures can be configured in a lot of different ways with different levels of complexity. We will work with fairly clean ANSI standard command structures that should require very little adaptation if moved to other DBMSs. Since we are developing a user registration web we will end up making use of the four standard SQL Data Manipulation commands.

INSERT

SELECT

UPDATE

DELETE

I am assuming you have all seen a fair amount of SQL in previous classes so I will not review the syntax and more general capabilities of these commands.

There are a few ways to create stored procedures. Most involve having fairly direct access to SQL Server on the server through some client software. In the interests of trying to develop examples that can be implemented at ISPs we will not work this way. ISPs can be quite restrictive about the levels of access and nature of connections they will allow to their servers.

We will develop our stored procedures from SQL statements embedded in ASP code. The ASP code will contain appropriate connection strings and permissions for SQL Server use. Then the ASPs will be uploaded to a web server and executed in order to develop the stored procedures on the database server.

You will each need to create one table and several stored procedures within a database called ASP_SQL. You need to make certain you can easily identify which table and stored procedures are yours. I am expecting, that you will use a format like that contained in the following table.

Object	Naming Convention	Example
Table name	yourInitials_UserRegistration	drf_UserRegistration
Stored Procedure name	yourInitials_functionalName	drf_InsertUser drf_DeleteUser drf_SelectUser drf_CheckUnique

You may end up numbering some of your efforts in case you make mistakes. If you want you can tell me which ones you want me to delete.

Some Syntax. Now we need to discuss some general issues about the syntax associated with creating stored procedures. It is probably important to remember that this is a rather abstract discussion preceding several examples that we will implement in subsequent weeks.

The syntax for creating stored procedures can be characterized by what appears in the following table. I am not presenting it in its most general form.

Command	Description
CREATE PROCEDURE yourInitials_funtionalName	required
@parameter_name1 datatype [= default_value], @parameter_name2 datatype [= default_value], ... @parameter_nameN datatype [= default_value],	required to pass parameters into the stored procedure typical to use might set default value though we usually won't
WITH recompile \| encryption	not using
AS	required
SQL command	required

There are obviously some things I have left out, but this is fairly accurate in its representation of the sorts of capabilities that are available.

Our examples of how this should actually be implemented are in subsequent webpages.