Hi Rockstar8,
Use below query.
SQL
DECLARE @out_desc VARCHAR(1000), @out_mesg VARCHAR(10)
DECLARE @name VARCHAR(20), @birthdate datetime, @email NVARCHAR(50)
DECLARE @body NVARCHAR(1000)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [name], [birthdate], [email]
FROM Customers
OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @CheckDay INT
IF DATENAME(DW, @birthdate) = 'Saturday'
BEGIN
SET @CheckDay = DATEPART(DAY,GETDATE() - 1)
END
ELSE IF DATENAME(DW, @birthdate) = 'Sunday'
BEGIN
SET @CheckDay = DATEPART(DAY,GETDATE() - 2)
END
ELSE
BEGIN
SET @CheckDay = DATEPART(DAY,GETDATE())
END
IF DATEPART(DAY,@birthdate) = @CheckDay AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())
BEGIN
SET @body = '<b>Happy Birthday ' + @name +
'</b><br />Many happy returns of the day'
+ '<br /><br />Customer Relationship Department'
EXEC sp_send_mail
'sender@abc.com',
'xxxxxxx',
@email,
'Birthday Wishes',
@body,
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
PRINT @out_mesg
PRINT @out_desc
END
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
END
CLOSE C1
DEALLOCATE C1
