asked on

SQL Cursor to send email with text and query result as attachment.

I need to write a proc to send out emails - the email list is in a table. One user gets only one email regardless of how many times her/his name appears on the table (I used select distinct). I am able to write a cursor to do this successfully. But I need to modify this so that the user now not only gets an email with the general email text(@body) but also with an attachment which would be the output of a SQL query. This query is what I am having trouble with- in the email table, there are fields "Email Address" and "user id". An example of a few records in the table:
Email Address user id
abc@123.com 1
abc@123.com 2
xyz@123.com 3
xyz@123.com 4
xyz@123.com 5
i have to query such that only one email is sent to each abc@123.com and xyz@123.com. For abc@123.com, she should get user id 1, 2 as an attachment. For xyz@123.com, she should get 3 4 5 as an attachment. How do I modify the existing cursor and/or the @query parameter to do this?
TIA for all the help..

Brendt Hess

The simplest method to handle this would be to make the query that you need the output of reference the email list table, and filter by the email address you have in your cursor. Modifying your query something like this:

... <your existing query here up to the WHERE statement> ...
INNER JOIN emailTable et
ON x.[user id] = et.[user id]
WHERE et.[Email Address] = @email
AND <rest of your existing WHERE clause if any>

spirose

ASKER

..........
..........

DECLARE EMAIL cursor for
select distinct Email_Address
from User_Email_Table
where send_email = 1

FOR READ ONLY
OPEN Email

FETCH NEXT FROM Emaill into @email
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC @rc = msdb.dbo.sp_send_dbmail
@profile_name = abc',
@blind_copy_recipients = @email,
@query = 'select userid from emailTable et INNER JOIN employeeTable x
ON x.[user id] = et.[user id]', -------THIS WILL NOT WORK
@attach_query_result_as_file = 1,
@body_format = 'HTML',
@body = @Message,
@subject = @subject,

FETCH NEXT FROM Email into @email
END
CLOSE Email
DEALLOCATE Email

Brendt Hess

The basic format is close, but (1) the query output is not filtered by the @email value, and ofo course I see no output but the user_id values. Obviously, you need to change field names to match your tables - is the value [user id] or user_id?

DECLARE EMAIL cursor for
select distinct Email_Address
from User_Email_Table
where send_email = 1

FOR READ ONLY
OPEN Email

FETCH NEXT FROM Emaill into @email
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC @rc = msdb.dbo.sp_send_dbmail
@profile_name = abc',
@blind_copy_recipients = @email,
@query = 'select userid from emailTable et INNER JOIN employeeTable x ON x.[user id] = et.[user id] WHERE @email = et.[Email_address]',
@attach_query_result_as_file = 1,
@body_format = 'HTML',
@body = @Message,
@subject = @subject,

FETCH NEXT FROM Email into @email
END
CLOSE Email
DEALLOCATE Email

spirose

ASKER

Sorry about unproper namings. Here is the correct field names, etc. Running this bit gives me error:
Error formatting query, probably invalid parameters.
Query execution failed. Must declatre the scalar variable "@email"

------
SET QUOTED_IDENTIFIER OFF

declare @email varchar(100)
......
.....
...

DECLARE EMAIL cursor for
select distinct EmailAddress
from emailTable
where emailSent = 'False'

FOR READ ONLY
OPEN Email

FETCH NEXT FROM Emaill into @email
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC @rc = msdb.dbo.sp_send_dbmail
@profile_name = abc',
@blind_copy_recipients = @email,
@query = "select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = 'False' and @email = et.[EmailAddress]",
@attach_query_result_as_file = 1,
@body_format = 'HTML',
@body = @Message,
@subject = @subject,

FETCH NEXT FROM Email into @email
END
CLOSE Email
DEALLOCATE Email

Brendt Hess

Sorry, that was my typo. This would need to read:

@query = "select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = 'False' and '" + @email + "'= et.[EmailAddress]",

spirose

ASKER

I get a syntax error: Incorrect syntax near '+@email+'

spirose

ASKER

I modified this to say:
@query = "select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = 'False' and et.[EmailAddress] = @email",.....

Now I get:
Error formatiing query, probably invalid parameters.

Must declare the scalar variable "@email"

spirose

ASKER

Fixed the error:

@query = "select userid from emailTable et INNER JOIN employeeTable x ON x.[userid] = et.[userid] WHERE emailSent = 'False' and et.[EmailAddress] = + '@email' ",
....

I get the email but it is not giving me the result of the @query ..it is blank. when I run the @query by itself it gives me 2 records...

spirose

ASKER

I do not think it is recognizing the value of @email since @query is actually outside the cursor..need some guidance

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spirose

ASKER

Perfect solution!! Thanks a bunch :)