Cursor Loop with HtmL Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor Loop with HtmL Table

Author

Topic

geo039
Starting Member

8 Posts

Posted - 2011-03-30 : 19:02:38

All, I have a query I created to send an html table to email, however what I need is to send emails to users in the table but not one email at a time per row. Supervisors may have multiple employees with evaluations due. How do I extract each supervisors group of employees and email them that portion of the table. I'm trying to figure out from my query below the cleanest way to get recipient info. Do I need another "cursor query" just containing the list of recipents and match them to the dataset in the unioned query?

DECLARE @FirstDayCurrentMonth DateTime
DECLARE @LastDayNextMonth DateTime
DECLARE @LastDayPreviousMonth DateTime
DECLARE @TodaysDate VARCHAR(20) 
 
SET @TodaysDate = DateAdd(day, datediff(day,0, GETDATE()), 0)
SET @FirstDayCurrentMonth = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@TodaysDate)-1),@TodaysDate),101))
SET @LastDayNextMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)))
SET @LastDayPreviousMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))    
DECLARE @tableHTML NVARCHAR(MAX)

; with CTE as E
(
SELECT 
	  descSection as 'SECTION',
	  COSTCENTER,
	  dbo.fncEmployeeName(v.ESPN) as EMPLOYEE, 
	  dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV,
	  'EVALDATE' = dbo.fncGetEvaluationDate(v.espn),
	  descEmpStatus as 'EMPLOYEESTATUS',
	  'EVALTYPE' = 
	  CASE
		WHEN descEmpStatus = 'Permanent' THEN 'Annual'
	  END	
FROM dbo.vCEDEmployees v
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @FirstDayCurrentMonth AND @LastDayNextMonth
AND descEmpStatus = 'Permanent'
AND cdEmpStatus IN (1, 2)
AND cdEmpType = 1

UNION ALL

SELECT 
	  descSection as 'SECTION',
	  COSTCENTER,
	  dbo.fncEmployeeName(v.ESPN) as EMPLOYEE, 
	  dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV,
	  'EVALDATE' = dbo.fncGetEvaluationDate(v.espn),
	  descEmpStatus as 'EMPLOYEESTATUS',
	  'EVALTYPE' = 
	  CASE
		WHEN descEmpStatus = 'Probation' THEN 'Probation'
	  END	
FROM dbo.vCEDEmployees v
   
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @TodaysDate AND @LastDayNextMonth
AND descEmpStatus = 'Probation'
AND cdEmpStatus IN (1, 2)
) 
Select @tableHTML = N'<H1>Employee Evaluations Due</H1>' + 
N'<table border="1">' +
    N'<tr><th>SECTION</th><th>COST CENTER</th><th>COST CENTER SUPV</th><th>SUPERVISOR</th><th>EMPLOYEE</th><th>STATUS</th><th>EVAL TYPE</th>' +
    N'<th>EVAL DATE</th>' +
    N'</tr>' +
CAST ( ( select 
				  TD = SECTION, '',
                  TD = COSTCENTER, '',
                  TD = COSTCENTERSUPV, '',
                  TD = SUPERVISOR, '',
				  TD = EMPLOYEE, '',
				  TD = EMPLOYEESTATUS, '',
				  TD = EVALTYPE, '',
				  TD = Convert(Varchar,EVALDATE,101), ''
		from CTE
ORDER BY 'EVALTYPE' DESC 
      FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
     
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'something',
@recipients='email',
    @subject = 'Evaluations Due',
    @body = @tableHTML,
    @body_format = 'HTML' ;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 10:25:19

Since you have to send the same attachments or message body to all recipients, I think it is a given that you will need to run the EXEC msdb.dbo.sp_send_dbmail multiple times. If that is true, then one way to make it more readable/maintainable might be to wrap it in a stored proc and pass in the query conditions as stored proc parameters, something like this:


create procedure dbo.SendStatusNoticeToSupervisor
   @supervisor_name varchar(255)
as

DECLARE @FirstDayCurrentMonth DateTime
DECLARE @LastDayNextMonth DateTime
DECLARE @LastDayPreviousMonth DateTime
DECLARE @TodaysDate VARCHAR(20) 
 
SET @TodaysDate = DateAdd(day, datediff(day,0, GETDATE()), 0)
SET @FirstDayCurrentMonth = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@TodaysDate)-1),@TodaysDate),101))
SET @LastDayNextMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)))
SET @LastDayPreviousMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))    
DECLARE @tableHTML NVARCHAR(MAX)

; with CTE as E
(
SELECT 
	  descSection as 'SECTION',
	  COSTCENTER,
	  dbo.fncEmployeeName(v.ESPN) as EMPLOYEE, 
	  dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV,
	  'EVALDATE' = dbo.fncGetEvaluationDate(v.espn),
	  descEmpStatus as 'EMPLOYEESTATUS',
	  'EVALTYPE' = 
	  CASE
		WHEN descEmpStatus = 'Permanent' THEN 'Annual'
	  END	
FROM dbo.vCEDEmployees v
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @FirstDayCurrentMonth AND @LastDayNextMonth
AND descEmpStatus = 'Permanent'
AND cdEmpStatus IN (1, 2)
AND cdEmpType = 1
AND v.supvusername = @supervisor_name

UNION ALL

SELECT 
	  descSection as 'SECTION',
	  COSTCENTER,
	  dbo.fncEmployeeName(v.ESPN) as EMPLOYEE, 
	  dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV,
	  'EVALDATE' = dbo.fncGetEvaluationDate(v.espn),
	  descEmpStatus as 'EMPLOYEESTATUS',
	  'EVALTYPE' = 
	  CASE
		WHEN descEmpStatus = 'Probation' THEN 'Probation'
	  END	
FROM dbo.vCEDEmployees v
   
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @TodaysDate AND @LastDayNextMonth
AND descEmpStatus = 'Probation'
AND cdEmpStatus IN (1, 2)
AND v.supvusername = @supervisor_name
) 
Select @tableHTML = N'<H1>Employee Evaluations Due</H1>' + 
N'<table border="1">' +
    N'<tr><th>SECTION</th><th>COST CENTER</th><th>COST CENTER SUPV</th><th>SUPERVISOR</th><th>EMPLOYEE</th><th>STATUS</th><th>EVAL TYPE</th>' +
    N'<th>EVAL DATE</th>' +
    N'</tr>' +
CAST ( ( select 
				  TD = SECTION, '',
                  TD = COSTCENTER, '',
                  TD = COSTCENTERSUPV, '',
                  TD = SUPERVISOR, '',
				  TD = EMPLOYEE, '',
				  TD = EMPLOYEESTATUS, '',
				  TD = EVALTYPE, '',
				  TD = Convert(Varchar,EVALDATE,101), ''
		from CTE
ORDER BY 'EVALTYPE' DESC 
      FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
     
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'something',
@recipients='email',
    @subject = 'Evaluations Due',
    @body = @tableHTML,
    @body_format = 'HTML' ;

You would need to specify that supervisor's e-mail as the only recipient.

Once you have this correctly working for one supervisor, then you will need call the stored proc for each supervisor.

Of course, the usual disclaimer that I have only a very limited understanding of your business rules, and hence this may be completely off-base still applies :)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-31 : 10:27:20

Am I understanding you that you want to email a single supervisor and let them know which evals are due?

So something like:

Supervisor: ajthepoolman
Evals due for: Ben Washington, Ben Jefferson, George Lincoln

then another email:

Supervisor: geo039
Evals due for: Taco John, Ronald McDonald, Wendy Popeye

Hey, it compiles.

geo039
Starting Member

8 Posts

Posted - 2011-03-31 : 17:13:36

Currently, I have the unioned query (CTE) sending emails to the the cursor recipient query (see modified query below) so each of the 44 supervisors are getting the entire list of evals due. What I want it to do is send only each supervisor their employees due. So each of the 44 supervisor emails would be different.

DECLARE @SECTION VARCHAR(100)        
DECLARE @COSTCENTER VARCHAR(100)
DECLARE @COSTCENTERSUPV VARCHAR(100)        
DECLARE @SUPERVISOR VARCHAR(100)        
DECLARE @SUPVUSERNAME VARCHAR(100)        
DECLARE @LINESPACE VARCHAR(1000)       
DECLARE @E_ADDRESS VARCHAR(50)        
DECLARE @E_RECIPIENTS VARCHAR(500)    
DECLARE @CC_RECIPIENTS VARCHAR(500)  
DECLARE @SRWTPPERSONNEL VARCHAR(500)  
DECLARE @E_SUBJECT VARCHAR(50)        
DECLARE @MESSAGE_1 VARCHAR(50)  
DECLARE @MESSAGE_1A VARCHAR(50)         
DECLARE @MESSAGE_2 VARCHAR(50)        
DECLARE @MESSAGE_3 VARCHAR(50)        
DECLARE @MESSAGE_4 VARCHAR(400)  
DECLARE @MESSAGE_5 VARCHAR(400)  
DECLARE @HYPERLINK VARCHAR(200)            
DECLARE @FirstDayCurrentMonth DateTime
DECLARE @LastDayNextMonth DateTime
DECLARE @LastDayPreviousMonth DateTime
DECLARE @TodaysDate VARCHAR(20) 
 
SET @TodaysDate = DateAdd(day, datediff(day,0, GETDATE()), 0)
SET @FirstDayCurrentMonth = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@TodaysDate)-1),@TodaysDate),101))
SET @LastDayNextMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)))
SET @LastDayPreviousMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))    
DECLARE @tableHTML NVARCHAR(MAX)

; with CTE as 
(

SELECT 
	  descSection as 'SECTION',
	  COSTCENTER,
	  dbo.fncEmployeeName(v.ESPN) as EMPLOYEE, 
	  dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV,
	  'EVALDATE' = dbo.fncGetEvaluationDate(v.espn),
	  descEmpStatus as 'EMPLOYEESTATUS',
	  'EVALTYPE' = 
	  CASE
		WHEN descEmpStatus = 'Permanent' THEN 'Annual'
	  END	
FROM dbo.vCEDEmployees v
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @FirstDayCurrentMonth AND @LastDayNextMonth
AND descEmpStatus = 'Permanent'
AND cdEmpStatus IN (1, 2)
AND cdEmpType = 1

UNION ALL

SELECT 
	  descSection as 'SECTION',
	  COSTCENTER,
	  dbo.fncEmployeeName(v.ESPN) as EMPLOYEE, 
	  dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV,
	  'EVALDATE' = dbo.fncGetEvaluationDate(v.espn),
	  descEmpStatus as 'EMPLOYEESTATUS',
	  'EVALTYPE' = 
	  CASE
		WHEN descEmpStatus = 'Probation' THEN 'Probation'
	  END	
FROM dbo.vCEDEmployees v
   
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @TodaysDate AND @LastDayNextMonth
AND descEmpStatus = 'Probation'
AND cdEmpStatus IN (1, 2)

) 

Select @tableHTML = N'<H1>Employee Evaluations Due</H1>' + 
N'<table border="1">' +
    N'<tr><th>SECTION</th><th>COST CENTER</th><th>COST CENTER SUPV</th><th>SUPERVISOR</th><th>EMPLOYEE</th><th>STATUS</th><th>EVAL TYPE</th>' +
    N'<th>EVAL DATE</th>' +
    N'</tr>' +
CAST ( ( select 
				  TD = SECTION, '',
                  TD = COSTCENTER, '',
                  TD = COSTCENTERSUPV, '',
                  TD = SUPERVISOR, '',
				  TD = EMPLOYEE, '',
				  TD = EMPLOYEESTATUS, '',
				  TD = EVALTYPE, '',
				  TD = Convert(Varchar,EVALDATE,101), ''
		from CTE 
ORDER BY 'EVALTYPE' DESC 
      FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
DECLARE CursorForEmail CURSOR FOR        

SELECT distinct dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  descSection as 'SECTION',
	  COSTCENTER,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV
FROM dbo.vCEDEmployees v
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @FirstDayCurrentMonth AND @LastDayNextMonth
AND descEmpStatus = 'Permanent'
AND cdEmpStatus IN (1, 2)
AND cdEmpType = 1

UNION ALL

SELECT distinct dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  descSection as 'SECTION',
	  COSTCENTER,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV
FROM dbo.vCEDEmployees v
   
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @TodaysDate AND @LastDayNextMonth
AND descEmpStatus = 'Probation'
AND cdEmpStatus IN (1, 2)
AND cdEmpType = 1

ORDER BY 'Supervisor' DESC
OPEN CursorForEmail        
FETCH NEXT FROM         
 CursorForEmail         
INTO         
 @SUPERVISOR,@SECTION,@COSTCENTER,@SUPVUSERNAME,@COSTCENTERSUPV
WHILE @@Fetch_Status >= 0        
BEGIN        

 
 EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'profile',
@recipients='email',
    @subject = 'Evaluations Due',
    @body = @tableHTML,
    @body_format = 'HTML' ;

 FETCH NEXT FROM         
  CursorForEmail         
 INTO         
   @SUPERVISOR,@SECTION,@COSTCENTER,@SUPVUSERNAME,@COSTCENTERSUPV    
END        
CLOSE CursorForEmail        
DEALLOCATE CursorForEmail

geo039
Starting Member

8 Posts

Posted - 2011-03-31 : 17:41:59

quote:
Originally posted by ajthepoolman

Am I understanding you that you want to email a single supervisor and let them know which evals are due?

So something like:

Supervisor: ajthepoolman
Evals due for: Ben Washington, Ben Jefferson, George Lincoln

then another email:

Supervisor: geo039
Evals due for: Taco John, Ronald McDonald, Wendy Popeye

Hey, it compiles.

Yes pretty much thats what i need it to do

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources