i have a result set that i am starting to email out. once users got wind of this they added to the spec that the email should be directed to the correct office that owns the data instead of emailing a list of info to everyone.
i am using sp_send_dbmail to send the sql result set to a generic email list. see the attached code snippet.
now i need to send an email per each office in the result set.
so what i did was join the office table and now i have access to the field called dropoff_email_recip
how can i send a mail with all the data pertaining to the particular office aggregated per office.??
i was thinking to create separate procs and put them in the scheduler to have each proc handle an office and include a where clause in each to pull only data each office will need...
seems like a wast since i have all the data in one result set
CREATE PROCEDURE [dbo].[spIncompleteRegistrationsGet]
-- Add the parameters for the stored procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @RegistrationID INT
DECLARE @RegistrationCount INT
DECLARE @EventName VARCHAR(100)
DECLARE @dropoff_email_recip VARCHAR(200)
DECLARE @SkyCode VARCHAR(50)
-- Insert statements for procedure here
--Verify that there are incomplete registrations in the past hour
select @RegistrationCount=COUNT(RegistrationID) from dbo.Registration
where Status < 3
and ModifiedDate > dateadd(mi, -120, GETDATE())
and ModifiedDate <= dateadd(mi, -60, GETDATE())
IF (@RegistrationCount > 0)
@dropoff_email_recip = office.dropoff_email_recip
JOIN dbo.Event ON dbo.Event.EventID = dbo.Registration.EventID
join dbo.office ON Event.ProductionOfficeID = office.OfficeID
where Status < 3
and Registration.ModifiedDate > dateadd(mi, -120, GETDATE())
and Registration.ModifiedDate <= dateadd(mi, -60, GETDATE())
select ContactID, SkyID, FirstName, LastName, Email, Phone, @EventName AS Event, @SkyCode As SkyCode
from Contact where ContactID IN
(select ContactID from dbo.RegistrationDelegate where RegistrationID = @RegistrationID)
select * from #tmpRegFallOff
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Incomplete Registrations</H1>' +
N'<table border="1">' +
N'<th>Phone</th> <th>Event</th> <th>SkyCode</th> </tr>' +
CAST ( ( SELECT td = isnull(ContactID,'null'), '',
td = isnull(SkyID,'null'), '',
td = isnull(FirstName,'null'), '',
td = isnull(LastName,'null'), '',
td = isnull(Email,'null'), '',
td = isnull(Phone,'null'), '',
td = isnull(Event,'null'), '',
td = isnull(SkyCode,'null'), ''
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +
@profile_name = 'SQLEmailer',
@subject = 'results for spIncompleteRegistrationsGet',
@body = @tableHTML,
@body_format = 'HTML' ;
SELECT ContactID FROM Contact WHERE ContactID = 0