Hi,

Everytime the store procedure is executed, I am getting  an email containing the following error:

====================================================

SQL Event Error:Abonnement d'essaie 15 jours

.An error occurred when attempting to run this SQL query:


sp_AddExpireDateNewOrExisting 2, 699, 15

The error returned isbject reference not set to an instance of an object.

==================================================

Looks like a variable is not initialised.

Any clue what could be the problem?

Belllow is the source code of the store procedure.

Thanks

Store procedure
=============================================================

USE [apsqadmin]

GO

/****** Object:  StoredProcedure [dbo].[sp_AddExpireDateNewOrExisting]    Script Date: 05/02/2012 07:01:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[sp_AddExpireDateNewOrExisting] 

@RoleID int, 

@UserID int, 

@Days int

AS

BEGIN

IF @Days > 0

BEGIN

-- if UserID doesn't belong to RoleID, then add to the table and set expiration date

 if (select count(*) from DNN_UserRoles where UserID=@UserID and RoleID=@RoleID) = 0

  BEGIN

-- if trial period (désire payer par la poste)

IF @DAYS = 15

BEGIN

insert into DNN_UserRoles(UserID,RoleID,ExpiryDate,IsTrialUsed,EffectiveDate)

values (@UserID,@RoleID,DATEADD(Day,@Days, getdate()),0,getdate()) 

END

ELSE

-- Si nouveau membre actif (Paypal)

BEGIN

DECLARE @date_time AS varchar(30)

SET @date_time = CONVERT(varchar,Year(getdate())) + '/04/01'; -- abonnement valide jusqu'au 1 avril de l'année suivante

insert into DNN_UserRoles(UserID,RoleID,ExpiryDate,IsTrialUsed,EffectiveDate)

values (@UserID,@RoleID,DATEADD(Year,1, @date_time),0,getdate()) 

END

  END 

 -- If UserID belongs to RoleID and ExpiryDate not expired (set ExpiryDate to 1 avril de l'année suivante)

 else if ((select ExpiryDate from DNN_UserRoles where UserID=@UserID and RoleID=@RoleID) > getdate()) 

  BEGIN

DECLARE @renewal_year_date AS datetime

SET @renewal_year_date = (SELECT ExpiryDate from DNN_UserRoles where UserID=@UserID and RoleID=@RoleID)

SELECT @renewal_year_date

SET @date_time = CONVERT(varchar,Year(@renewal_year_date)) + '/04/01'; -- abonnement valide jusqu'au 1 avril de l'année suivante

    update DNN_UserRoles set ExpiryDate = DATEADD(Year,1, @date_time) where UserID=@UserID and RoleID=@RoleID

  END

 else

 -- If UserID belongs to RoleID and ExpiryDate already expired (set ExpiryDate 1 avril de l'année suivante)

  BEGIN

SET @date_time = CONVERT(varchar,Year(getdate())) + '/04/01'; -- abonnement valide jusqu'au 1 avril de l'année suivante

    update DNN_UserRoles set ExpiryDate =  DATEADD(Year,1, @date_time) where UserID=@UserID and RoleID=@RoleID

END

END

END