-3

I have a table in SQL server end_date and start_date column I want to send email to the user before seven days and one day before that licence is expiring. how

Singh
  • 1
  • 2
    You don't do this in a proc. You do this with a SQL Server Agent job that runs periodically (or perhaps through a message queue). – Gordon Linoff Feb 17 '19 at 12:32
  • 1
    Or either windows service! – Prashant Pimpale Feb 17 '19 at 12:34
  • If that's what you want to do, what's stopping you? You don't need to tell us what you want to do; you can just do it. Do you have a question here? If so, please do ask one so we can help you. Do show us what you've already tried and where you got stuck. Thanks. – Thom A Feb 17 '19 at 13:06

1 Answers1

0

I want to send email to the user before seven days and one day before that licence is expiring.

select * from mytable
where 
(CAST(end_date as DATE) = DateAdd(DD,1,GETDATE()))
OR
(CAST(end_date as DATE) = DateAdd(DD,7,GETDATE()))

So you could break that down into two separate queries if your emailing out notices of expiration for different wording.

You do need to run this each day, either with a scheduler, or as other posters have pointed out using an agent job.

I haven't tried this, so you may need to put the CAST around the DateAdd as well. I'm not sure what you're datatypes are from the question. It's usually better to post the table or parts of the table so we can better answer the question.

If you want a poor mans scheduler, which is a pretty bad way to implement this, you can do something like this borrowed from here: 

CREATE PROCEDURE MyBackgroundTask
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- The interval between cleanup attempts
    declare @timeToRun nvarchar(50)
    set @timeToRun = '03:33:33'

    while 1 = 1
    begin
        waitfor time @timeToRun
        begin
            execute [MyDatabaseName].[dbo].[MyDatabaseStoredProcedure];
        end
    end
END
Mike
  • 3,186
  • 3
  • 26
  • 32