Inside Technique : Adding Site Registration : The Stored Procedures

We built all the database manipulations directly into the SQL database as stored procedures. This approach allows us to test the database independently from the ASP scripts. Therefore, our first step is to explain each of the stored procedures and how they are used in the system.

Once we are finish exploring the stored procedures, we explain how these procedures are manipulated from the ASP scripts. For the most part, the ASP scripts are quite straightforward, but they do demonstrate techniques for processing forms on the server. However, in a few cases we did include validation logic that could easily be incorporated into the backend database rather than manipulated through script.

First, let's look at the stored procedures we created for our membership database:

Stored Procedure	Description
sp_adduser	This stored procedure first verifies a user does not exist and then adds the user to the database.
sp_changepassword	This stored procedure is used to change a user's password.
sp_getuser	This stored procedure is used by the site manager to returns all the information for a specified user.
sp_logonuser	This stored procedure takes a user name and password and tries to log in a user. This also updates the log-in count for the user and the time stamp for the last visit.
sp_saveuser	This stored procedure is used by the site manager to update a user's record.

We start by explaining the two stored procedures for adding and logging in users.

sp_adduser

Adding a user to the database is a very simple process with one simple caveat - you must first ensure the specified user name is available. We accomplish this with a simple check against the user table before adding the record. If no record exists for the specified user-name we add the user. If a user exists, we return a user-id of -1 which signifies the duplicate.

Create Procedure sp_adduser
  @u_name varchar(50),
  @u_password varchar(12),
  @u_firstname varchar(50),
  @u_lastname varchar(50),
  @u_email varchar(50),
  @u_id int output
As
  set nocount on
  if not exists(select u_id from t_user where u_name=@u_name)
    begin
      INSERT INTO t_user(u_name, u_password, u_firstname, u_lastname, u_email) 
        VALUES (@u_name, @u_password, @u_firstname, @u_lastname, @u_email)
      select @u_id=@@identity
    end
  else
    select @u_id=-1
  return 

This stored procedure is missing data validation. For example, there is no validation that the password meets a certain constraint (eg., between 4 and 12 characters). We currently do this validation in our ASP scripts (explained later). In a production system, such constraints may be better defined within the stored procedure with the addition of more detailed return codes. In addition, a production system should probably encrypt the user's password. In this demonstration, the password is stored as entered.

sp_logonuser

This stored procedure is very simple. Given a user name and password, we locate and return the corresponding record:

Create Procedure sp_logonuser
  @u_name varchar(50), 
  @u_password varchar(12)
As
  set nocount on
  UPDATE t_user SET u_lastDate = GETDATE(), u_logcount = u_logcount + 1 
    WHERE (u_name = @u_name) AND (u_password = @u_password)
  SELECT * from t_user where u_name=@u_name and u_password=@u_password
  return 

The first query in this procedure increments the number of visits and updates the last log-in date. Once updated the user record itself is returned to the user. We do not need to check for the existance of the user record as this is handled automatically by the where clause that tests for a matching user-name and password. If there is no match an empty recordset is returned.

Next we explain the three stored procedures related to administration.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.