CODEWITHSUNDEEP
sql-serverstored-procedures
user1176737
user1176737

Reputation: 291

SQL Server : DBMail Stored Procedure

I am a novice with SQL Server, and I have created a stored procedure from an amalgamation of posts.

Here is the stored procedure - it all works well, apart from the fact that it uses the number of days from the first entry, and doesn't change it subsequently, so I get the same @numberofdays for each email.

 ALTER PROCEDURE [dbo].[RenewalsCheck]
 AS
        DECLARE @CompanyName nvarchar (50) = NULL
        DECLARE @ProductKey nvarchar (50) = NULL
        DECLARE @ProductName nvarchar (50) = NULL
        DECLARE @MaintenanceStartDate nvarchar (10) = NULL
        DECLARE @MaintenanceEndDate nvarchar (10) = NULL
        DECLARE @Result nvarchar (10) = NULL
        DECLARE @Emailed int = NULL
        DECLARE @Sent nvarchar (10) = NULL
        DECLARE @Body nvarchar (max) = NULL
        DECLARE @Subject nvarchar (max) = NULL
        DECLARE @SalesManEmail nvarchar (max) = NULL
        DECLARE @NumberOfDays nvarchar (5) = NULL
        DECLARE @FollowUp int = NULL

DECLARE cEMail CURSOR LOCAL FAST_FORWARD FOR

Select 

[CompanyName],
[Product Key],
[Product Name],
[Maintenance Start Date],
[Maintenance End Date],
[Emailed],
[SalesManEmail],
[FollowUp EC Emailed]

FROM dbo.Product

WHERE

datediff(d,GETDATE(),[Maintenance End Date]) < 15 AND [FollowUp EC Emailed]  = '0'

SELECT
@NumberOfDays = datediff(d,GETDATE(),[Maintenance End Date])
FROM dbo.Product
WHERE datediff(d,GETDATE(),[Maintenance End Date]) < 15 AND [FollowUp EC Emailed] = '0'

OPEN cEmail;

WHILE 1 = 1
BEGIN

    FETCH NEXT FROM cEmail INTO @CompanyName, @ProductKey, @ProductName, @MaintenanceStartDate ,@MaintenanceEndDate,
    @Emailed, @SalesManEmail;

    IF @@FETCH_STATUS = -1 BREAK;

    SET @Body = 'The Product ' + @ProductName + ' with a product key of ' + @ProductKey + ' Expires in ' + @NumberOfDays + ' days.'
    SET @Subject = 'Product Expiring in ' + @NumberOfDays + ' at ' + @CompanyName

    EXECUTE msdb.dbo.sp_send_dbmail @profile_name='Jason',
                                    @subject = @Subject,
                                    @recipients = @SalesManEmail,
                                    @copy_recipients ='',
                                    @body = @body;

END

    UPDATE [Product]
    SET [FollowUp EC Emailed] = '1'
    WHERE datediff(d,GETDATE(),[Maintenance End Date]) < 15 AND [FollowUp EC Emailed] = '0'

CLOSE cEmail;
DEALLOCATE cEmail;

I know some of you will question how it's setup and may have better ways of doing this, but I am new, and it is working apart from one tiny section.

Any help is greatly appreciated.

Regards

Jason

Upvotes: 0

Views: 131

Answers (1)

Ian Kenney
Ian Kenney

Reputation: 6426

The problem is that the number of days is set once outside the cursor loop

you can include it in the cursor query by adding a case field - something like:

Select 
[CompanyName],
[Product Key],
[Product Name],
[Maintenance Start Date],
[Maintenance End Date],
[Emailed],
[SalesManEmail],
[FollowUp EC Emailed],
case when datediff(d,GETDATE(),[Maintenance End Date]) < 15 AND [FollowUp EC Emailed] = '0' datediff(d,GETDATE(),[Maintenance End Date]) end NumberDays
FROM dbo.Product

you will also need to add the assignment for @NumberOfDays into you FETCH NEXT

Upvotes: 1

Related Questions