[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

sql coding logic in a stored proc?

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

thoughts?


CREATE PROCEDURE [dbo].[spIncompleteRegistrationsGet]           
 -- Add the parameters for the stored procedure here          
          
AS          
BEGIN          
 -- 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)          
 BEGIN          
  select @RegistrationID=RegistrationID, 
		 @EventName=Event.Name, 
		 @SkyCode=SkySessionCode,
		 @dropoff_email_recip =  office.dropoff_email_recip
  from dbo.Registration           
	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       
   into #tmpRegFallOff      
   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'<tr><th>ContactID</th><th>SkyID</th>' +      
   N'<th>FirstName</th><th>LastName</th><th>Email</th>' +      
   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'), ''      
       FROM #tmpRegFallOff       
       FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +      
   N'</table>' ;      
      
  EXEC msdb.dbo.sp_send_dbmail       
   @profile_name = 'SQLEmailer',      
   @recipients='USIQPCDotnetTeam@iqpc.com',      
   @subject = 'results for spIncompleteRegistrationsGet',      
   @body = @tableHTML,      
   @body_format = 'HTML' ;      
            
         
 END          
 ELSE          
  SELECT ContactID FROM Contact WHERE ContactID = 0          
END

Open in new window

0
supergirl2008
Asked:
supergirl2008
1 Solution
 
dbidbaCommented:
OK, I'll take a shot.  Of course, it is completely untested.

You can try adding a cursor to loop through each office to pull the data and mail it. I assumed that SkyCode is what associates your office list to your data to mail.


 ...
 IF (@RegistrationCount > 0)
 BEGIN
   -- ----------------------------------------------------------------------------
   -- Stage the data.
   -- ----------------------------------------------------------------------------
   select ContactID, SkyID, FirstName, LastName, Email, Phone, @EventName AS Event, @SkyCode As SkyCode
   into #tmpRegFallOff
   from Contact where ContactID IN
   (select ContactID from dbo.RegistrationDelegate where RegistrationID = @RegistrationID)

   select * from  #tmpRegFallOff
   DECLARE @tableHTML  NVARCHAR(MAX) ;
   -- ----------------------------------------------------------------------------
   -- Open a cursor to loop through each office.
   -- ----------------------------------------------------------------------------
   DECLARE cOffice CURSOR
   READ_ONLY
   FOR
     select
         RegistrationID
        ,Event.Name
        ,SkySessionCode
        ,office.dropoff_email_recip
      from
         dbo.Registration
          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())

   OPEN cOffice

   FETCH NEXT FROM cOffice INTO
         @RegistrationID
        ,@EventName
        ,@SkyCode
        ,@dropoff_email_recip

   WHILE (@@fetch_status <> -1)
   BEGIN
       IF (@@fetch_status <> -2)
       BEGIN
         -- ----------------------------------------------------------------------------
         -- Pull the data for the current office.
         -- ----------------------------------------------------------------------------
         SET @tableHTML =
         N'<H1>Incomplete Registrations</H1>' +
         N'<table border="1">' +
         N'<tr><th>ContactID</th><th>SkyID</th>' +
         N'<th>FirstName</th><th>LastName</th><th>Email</th>' +
         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'), ''
             FROM #tmpRegFallOff
             where SkyCode = @SkyCode
             FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +
         N'</table>' ;
         -- ----------------------------------------------------------------------------
         -- Mail to the current office.
         -- ----------------------------------------------------------------------------
         EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'SQLEmailer',
         @recipients=@dropoff_email_recip,
         @subject = 'results for spIncompleteRegistrationsGet',
         @body = @tableHTML,
         @body_format = 'HTML' ;
       END
      FETCH NEXT FROM cOffice INTO
            @RegistrationID
           ,@EventName
           ,@SkyCode
           ,@dropoff_email_recip
   END
 END

...
-- ----------------------------------------------------------------------------
-- Cleanup.
-- ----------------------------------------------------------------------------
CLOSE cOffice
DEALLOCATE cOffice


0
 
SharathData EngineerCommented:
My suggestion is pass OfficeID as paramter to your SP and invoke this SP with OfficeID as paramter whenever you want.

CREATE PROCEDURE [dbo].[spIncompleteRegistrationsGet] 
@OfficeId int
 -- Add the parameters for the stored procedure here          
          
AS          
BEGIN          
 -- 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)          
 BEGIN          
  select @RegistrationID=RegistrationID, 
                 @EventName=Event.Name, 
                 @SkyCode=SkySessionCode,
                 @dropoff_email_recip =  office.dropoff_email_recip
  from dbo.Registration           
        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())      
   and office.OfficeID = @OfficeID
             
             
   select ContactID, SkyID, FirstName, LastName, Email, Phone, @EventName AS Event, @SkyCode As SkyCode       
   into #tmpRegFallOff      
   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'<tr><th>ContactID</th><th>SkyID</th>' +      
   N'<th>FirstName</th><th>LastName</th><th>Email</th>' +      
   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'), ''      
       FROM #tmpRegFallOff       
       FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +      
   N'</table>' ;      
      
  EXEC msdb.dbo.sp_send_dbmail       
   @profile_name = 'SQLEmailer',      
   @recipients='USIQPCDotnetTeam@iqpc.com',      
   @subject = 'results for spIncompleteRegistrationsGet',      
   @body = @tableHTML,      
   @body_format = 'HTML' ;      
            
         
 END          
 ELSE          
  SELECT ContactID FROM Contact WHERE ContactID = 0          
END

Open in new window

0
 
supergirl2008Author Commented:
i used a while loop and am testing but the idea is the close to what dbidba: said... so thanks...
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now