Extrend SP to check for Expiry date prior to updating?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

jason_at_catamaranco.com
Date: 08/20/04 

Date: Fri, 20 Aug 2004 11:20:51 -0400

Is it possible to extend this User Login SP to return a error if the column
u_ExpiryDate is < Todays Date and based on this send him to an
registration-update page.....?
I have included sample: SP, table, an calling asp SP code:

(1) - Stored Procedure
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create Procedure sp_logonuser
 @u_name varchar(50),
 @u_password varchar(12)
As
 set nocount on
    BEGIN

 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

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

(2) - TABLE: t_user
----------------
REATE TABLE [t_user] (
 [u_id] [int] IDENTITY (1, 1) NOT NULL ,
 [u_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [u_password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [u_firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
 [u_lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [u_logcount] [int] NOT NULL CONSTRAINT [DF_t_user_u_logcount] DEFAULT (1),
 [u_createDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_createDate]
DEFAULT (getdate()),
 [u_lastDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_lastDate] DEFAULT
(getdate()),
 [u_SecurityLevel] [int] NULL ,
 [u_SourceID] [int] NULL ,
 [u_YachtPlacementID] [int] NULL ,
 [u_NationID] [int] NULL ,
 [u_StateID] [int] NULL ,
 [u_BrokerID] [int] NULL ,
 [u_ExpiryDate] [datetime] NULL ,
 [u_Comment] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [u_CurrentURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [u_FromURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [u_VisitorIP] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [u_AcceptCookies] [int] NULL ,
 [u_AcceptEmail] [int] NULL ,
 [u_EmailOK] [int] NULL ,
 [u_Browser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [u_OS] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [u_TimeFrame] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [u_IDStatus] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 CONSTRAINT [PK_t_user_name] PRIMARY KEY NONCLUSTERED
 (
  [u_id]
 ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
 CONSTRAINT [IX_t_user_name] UNIQUE NONCLUSTERED
 (
  [u_name]
 ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
 CONSTRAINT [FK_t_user_Broker] FOREIGN KEY
 (
  [u_BrokerID]
 ) REFERENCES [Broker] (
  [BrokerID]
 ) ON UPDATE CASCADE ,
 CONSTRAINT [FK_t_user_Nation] FOREIGN KEY
 (
  [u_NationID]
 ) REFERENCES [Nation] (
  [NationID]
 ) ON UPDATE CASCADE ,
 CONSTRAINT [FK_t_user_Source] FOREIGN KEY
 (
  [u_SourceID]
 ) REFERENCES [Source] (
  [SourceID]
 ) ON UPDATE CASCADE ,
 CONSTRAINT [FK_t_user_State] FOREIGN KEY
 (
  [u_StateID]
 ) REFERENCES [State] (
  [StateID]
 ) ON UPDATE CASCADE ,
 CONSTRAINT [FK_t_user_YachtPlacement] FOREIGN KEY
 (
  [u_YachtPlacementID]
 ) REFERENCES [YachtPlacement] (
  [YppID]
 ) ON UPDATE CASCADE
) ON [PRIMARY]
GO

(3) Dim oCmd, oConn, oRS
   set oConn = GetConnection()
   Set oCmd = GetStoredProcedure(oConn,"spr_logonuser")
   oCmd.Parameters.append oCmd.CreateParameter("u_name", adVarChar,
adParamInput,50,sName)
   '//oCmd.Parameters.append oCmd.CreateParameter("u_password", adVarChar,
adParamInput,12,spassword)
   set oRS = oCmd.execute()
   if oRS.eof then
    sError = "<BR>Invalid User Email Address. Please enter the email you
signed up with originally"
   else '//SUCCESSFUL
        response.redirect SelectedPage.asp
End If

Relevant Pages