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 is
bject 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