T-SQL Cursors and Send Mail

Hi,

I have just started trying to use Cursors and SQL Mail as we have a requirement whereby each night we need to run a script to return all records created over a specified number of days and then email the results to the user that created them. This needs to repeat for each distinct user found in the Created By column so that they only receive a copy of the records they've created, and not those created by someone else.

I have managed to put together a script which will email the results to me but this is tied to a specific user:

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml =CAST((SELECT tb1.record_id AS 'td', '', tb1.names AS 'td', '', 
tb2.title AS 'td', '', tb2.address AS 'td', '', tb2.country AS 'td', '', tb2.postcode AS 'td', '' FROM table1 tb1, table2 tb2 WHERE tb1.record_id = tb2.record_id AND tb2.date_created BETWEEN getdate()-7 AND getdate() AND tb2.created_by = 'USERA' FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

SET @body = '<html><H2>Records Created</H2><body><table border = 1><tr><th>Record ID</th><th>Record Name</th>' 
set @body = @body + '<th>Title</th><th>Address</th><th>Country</th><th>Postcode</th></tr>'
set @body = @body + @xml + '</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
	@execute_query_database = 'DATABASE NAME',
	@profile_name = 'MAIL PROFILE',
	@recipients = 'RECIPIENT EMAIL ADDRESS',
	@subject = 'Records Created',
	@body = @body,
	@body_format = 'HTML'

Open in new window


I have also managed to create a cursor which will print the info to the screen:

DECLARE @RecID as nvarchar(20)
DECLARE @Name as nvarchar(100)
DECLARE @Title as nvarchar(100)
DECLARE @Address as nvarchar(100)
DECLARE @Country as nvarchar(100)
DECLARE @Postcode as nvarchar(100)

Declare My_Test_Cursor CURSOR FAST_FORWARD FOR

SELECT tb1.record_id, tb1.names, tb2.title, tb2.address, tb2.country, tb2.postcode FROM table1 tb1, table2 tb2 WHERE tb1.record_id = tb2.record_id AND tb2.date_created BETWEEN getdate()-7 AND getdate() AND tb2.created_by = 'USERA'

OPEN My_Test_Cursor
FETCH NEXT FROM My_Test_Cursor
INTO @RecID, @Name, @Title, @Address, @Country, @Postcode

WHILE @@FETCH_STATUS = 0
BEGIN
      print 'RecID: ' + @RecID
      print 'Name: ' + @Name
      print 'Title: ' + @Title
      print 'Address: ' + @Address
      print 'Country: ' + @Country
      print 'Post Code: ' + @Postcode
      print CHAR(13)

     FETCH NEXT FROM My_Test_Cursor
     INTO @RecID, @Name, @Title, @Address, @Country, @Postcode
END

CLOSE My_Test_Cursor
DEALLOCATE My_Test_Cursor

Open in new window


The trouble is......I don't know how to marry the two together so that each user ends up being emailed the list of records they created during the time period.  Any ideas?!
LVL 1
SquareOneResourcesAsked:
Who is Participating?
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Try this (sorry no time for debug)
declare @store_header varchar(max), @store_footer varchar(max), @store_body varchar(max)

with S(record_id, names, title, address, country, postcode )
as
(
select
tb1.record_id, tb1.names, tb2.title, tb2.address, tb2.country, tb2.postcode 
FROM table1 tb1, table2 tb2 
WHERE tb1.record_id = tb2.record_id AND 
(tb2.date_created BETWEEN dateadd(dd, -7, getdate()) AND getdate()) AND 
tb2.created_by = 'USERA'
)

select @store_body = '',
@store_header = 
'<html><H2>Records Created</H2><body><table border = 1><tr><th>Record ID</th><th>Record Name</th><th>Title</th><th>Address</th><th>Country</th><th>Postcode</th></tr>',
@store_footer = '</table></body></html>

select @store_body = @store_body +
'<tr><td>' + isnull(record_id, '') +'</td>' + '<td>' + isnull(names, '') + '</td>' + '<td>' + isnull(title, '') + '</td>' + '<td>' + isnull(address, '') + '</td>' + '<td>'+ isnull(country, '') +'</td>' + '<td>' + isnull(postcode, '') +'</td></tr>'
from S

exec @store_body = @store_header + @store_body + @store_footer

exec msdb.dbo.sp_send_dbmail
	@execute_query_database = 'DATABASE NAME',
	@profile_name = 'MAIL PROFILE',
	@recipients = 'RECIPIENT EMAIL ADDRESS',
	@subject = 'Records Created',
	@body = @store_body,
	@body_format = 'HTML'

Open in new window

0
 
Ryan McCauleyData and Analytics ManagerCommented:
In this example, where would you get USERA's email address? I see that the second query shows just their records, but you'll also need to have email addresses handy for all the other users who have records in your table.

Once you have that, you can execute the second cursor and email each person their records. Do you want to send them a separate email for each record, or a summary of everything?
0
 
SquareOneResourcesAuthor Commented:
Thanks.

Right, I have managed to compile the following code which combines the cursor and send mail process; the first part of the cursor returns records created upto 7 days ago, I am now trying to add a second section to the email which shows records created 7-14 days ago.  This works IF there are results from both SELECT scripts, however if there are none in the second script then the email is blank.

Can I use an IF...EXISTS statement to return the data if its there, but then enter a line of text if its not.  I need to then further expand this to return results going back up to 4 weeks so I can just repeat the process once I get it right for the first two weeks.

Here's my code so far:

DECLARE @zUserID AS NVARCHAR(3)
DECLARE @zUserName AS NVARCHAR(100)
DECLARE @zEmailAddress AS NVARCHAR(100)

DECLARE My_Test_Cursor CURSOR FAST_FORWARD FOR

[i]<<SELECT SCRIPT GOES HERE>>[/i]				

OPEN My_Test_Cursor
FETCH NEXT FROM My_Test_Cursor
INTO @zUserID, @zUserName, @zEmailAddress

WHILE @@FETCH_STATUS = 0
BEGIN

     DECLARE @xml NVARCHAR(MAX)
	 DECLARE @body NVARCHAR(MAX)
	 DECLARE @subject NVARCHAR(MAX)

SET @xml =CAST(([i]<<SELECT SCRIPT GOES HERE>>[/i] FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

SET @body = '<html><style type="text/css">body {font-family: Trebuchet MS; font-size: 12px;} h2 {font-family: Trebuchet MS; ' 
SET @body = @body + 'font-size: 14px; font-weight: bold;} th {font-weight: bold; text-align: left; background-color: #8CD2EB;}</style><H2>Heading 1</H2>'
SET @body = @body + '<body><table border = 1><tr><th>Candidate ID</th><th>Candidate Name</th><th>Title</th><th>Company</th><th>Date1</th><th>Date2</th><th>Record ID</th><th>Created By</th></tr>'
SET @body = @body + @xml + '</table></body></html>'

SET @xml =CAST(([i]<<SELECT SCRIPT GOES HERE>>[/i] FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

SET @body = @body + '<html><style type="text/css">body {font-family: Trebuchet MS; font-size: 12px;} h2 {font-family: Trebuchet MS; ' 
SET @body = @body + 'font-size: 14px; font-weight: bold;} th {font-weight: bold; text-align: left; background-color: #8CD2EB;}</style><H2>Heading 2</H2>'
SET @body = @body + '<body><table border = 1><tr><th>Record ID</th><th>Name</th><th>Title</th><th>Company</th><th>Date1</th><th>Date2</th><th>Record ID</th><th>Created By</th></tr>'
SET @body = @body + @xml + '</table></body></html>'
	 	 
SET @subject = 'Created By ' + @zUserName

EXEC msdb.dbo.sp_send_dbmail
@execute_query_database = '[i]<<DATABASE>>[/i]',
		 @profile_name = '[i]<<MAIL PROFILE>>[/i]',
		 @recipients = '[i]<<RECIPIENTS>>[/i]',
		 @subject = @subject,
		 @body = @body,
		 @body_format = 'HTML'
     
FETCH NEXT FROM My_Test_Cursor
INTO @zUserID, @zUserName, @zEmailAddress

END

CLOSE My_Test_Cursor
DEALLOCATE My_Test_Cursor

Open in new window

0
 
SquareOneResourcesAuthor Commented:
A little tweaking and expanding on a couple of areas and this worked.  Thank you!  I will post the code soon but don't have time to "de-personalise" it right now.

Give me a couple of days.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.