Inside Technique : Adding Site Registration : Administration Procedures

There are three stored procedures related to site administration. One, sp_changepassword, is intended for use by your members, and the other two, sp_getuser and sp_saveuser are intended for use by the site administrator.

sp_changepassword

This simple stored procedure is used to change the user's password:

Create Procedure sp_ChangePassword
	@u_id int,
	@old_password varchar(12),
	@new_password varchar(12),
	@success int output
As
 set nocount on
 if exists 
 (SELECT u_id FROM t_user WHERE (u_id = @u_id) AND (u_password = @old_password))
  begin
   UPDATE t_user SET u_password = @new_password WHERE (u_id = @u_id)
   select @success = 1
  end
 else
  select @success =  -1
 return 

Before updating the password, first we verify that the user entered the correct password for their user-id. If the password is correct, we simply update the password field. If the old password is entered incorrectly, we return a failure code.

sp_getuser

This is a very simple and self-explanatory stored procedure that returns the complete user record for a specified user-id.

Create Procedure sp_GetUser
	@u_id int
As
	set nocount on
	select * from t_user where u_id=@u_id
	return 

We use this stored procedure to allow the site administrator to retrieve any user's record for editing. To complement this stored procedure, we created sp_saveuser which is used to update the user's record.

sp_saveuser

This stored procedure is used to update all the fields (except the user's id) for any particular user:

Create Procedure sp_SaveUser
	@u_id int,
	@u_name varchar(50),
	@u_password varchar(12),
	@u_firstname varchar(50),
	@u_lastname varchar(50),
	@u_email varchar(50)
As
 set nocount on
 if not exists(select u_id from t_user where u_name=@u_name and u_id<>@u_id) 
  UPDATE t_user 
  SET u_name = @u_name, u_password = @u_password, u_firstname = @u_firstname,  
      u_lastname = @u_lastname, u_email = @u_email 
      WHERE (u_id = @u_id)
  return 

Before updating the record, we first verify that no other user is already using a particular name. If none exists, we update all the fields for the specified user.

This concludes our introduction to all the stored procedures. Next we walk you through the ASP scripts used to coordinate and inteface with the backend database.

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