CODEWITHSUNDEEP
sqlsql-serveremailrdbms
learner
learner

Reputation: 13

How to send email based group

How to send email based group?
I have three data

group 1 = [email protected] = 2 select items
group 2 = [email protected] = 1 select items

I use the following method , the first group I get 2 items
But in groups of two I get 3 items

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.procedure2
AS
BEGIN

DECLARE @tableHTML nvarchar(max)
DECLARE @Table NVARCHAR(MAX) = N''

DECLARE @tmp_list_mail table
(
  member_id INT,
  email VARCHAR(30) 
) 
insert @tmp_list_mail(member_id,email)
select c.member_id, c.email from [members]c 
inner join maintenance d on c.member_id =d.member_id
group by  c.member_id, c.email


SET NOCOUNT ON
DECLARE @member_id int, @email varchar(30)
DECLARE Contacts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT member_id, email,25,15 FROM @tmp_list_mail;
OPEN Contacts;
FETCH NEXT FROM Contacts INTO @member_id, @email
  WHILE (@@FETCH_STATUS=0)
  BEGIN
     SELECT @Table = @Table +'<tr style="background-color:'+CASE WHEN (ROW_NUMBER() OVER (ORDER BY a.maintenance_id))%2 =1 THEN '#DCDCDC' ELSE '#F8F8FF' END +';">' +
                      '<td>' + CONVERT(VARCHAR(11), ROW_NUMBER() OVER(ORDER BY a.maintenance_id ASC))  + '</td>' +
                      '<td>' + b.vehicle_number+ '</td>' +
                      '<td>' +CONVERT(VARCHAR(11),a.tax,106)+ '</td>' +
                      '</tr>'
                      from maintenance a   
                      left join vehicles b on a.vehicle_id=b.vehicle_id  
                      left join [members] c on a.member_id =c.member_id     
                      where  c.member_id =@member_id and
                      DATEDIFF(DAY, getdate(),a.tax) =25 

                      ORDER BY a.member_id asc

                      SET @tableHTML = 
                      N'<H3><font color="Red">Maintenance List '+@email+'  </H3>' +
                      N'<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:#000000;font-family:arial,helvetica,sans-serif;text-align:center;" >' +
                      N'<tr style ="font-size: 14px;font-weight: normal;background: #64950;">
                      <th>No.</th>
                      <th>Truck Number</th>
                      <th>Pajak</th>
                      </tr>' + @Table + N'</table>'   
 EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Windows Live Mail'
      , @recipients =    @email
      , @subject = 'Maintenance Alert' 
      , @body = @tableHTML
      , @body_format = 'HTML' 
    END                    
FETCH NEXT FROM Contacts INTO @member_id, @email
CLOSE Contacts;
DEALLOCATE Contacts;
SET NOCOUNT OFF
END

Upvotes: 1

Views: 226

Answers (1)

cha
cha

Reputation: 10411

Very easy. You need to reset the @Table variable, like this:

.....
FETCH NEXT FROM Contacts INTO @member_id, @email
  WHILE (@@FETCH_STATUS=0)
  BEGIN
     SET @Table = '' -- Add this line
     SELECT @Table = @Table + ......

Upvotes: 1

Related Questions