Hi,
I have a select statement which brings back several fields and several
columns. Within one of these columns is an email address. I want to be
able to cycle through each record in the select statement and email the
details attached to their email address. My SP is below:-
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
I know you can use the following to send emails:-
@.sbj varchar(200),
@.msg varchar(2000),
@.recipient varchar(50)
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
but I want to incorporate that xp_sendmail with the select statement.
If anyone has ever done this before and can give me some pointers it would
be greatly appreciated.
Thanks
DamonI think you will need to use a cursor.
Here's some help with your SELECT statement, by the way. It's still largely
unreadable because of the superfluous prefixes and mismatched column names,
but it should be a little easier to tackle. Note that I changed the WHERE
clause to apply transformation to the constant, instead ofto the column, and
kept with the tradition of object-operator-value instead of
value-operator-object. getdate()>column+x is very difficult to process, at
least for me. If the table is huge, you may find an advantage in declaring
a variable of smalldatetime up front and calculating GETDATE()+7 and storing
it in a constant. However, if there is no index on sd_renewal_date, it's
probably all moot.
SELECT
Reg_No = s.s_id,
URN = d.sd_urn,
OFFICER = d.sd_reviewing_officer,
RENEWAL_DATE = d.sd_renewal_date,
OFFICER_EMAIL = e.e_officer_email
FROM
dbo.tbl_surveillance s
INNER JOIN
dbo.tbl_surveillance_dates d
ON
s.s_id = d.sd_s_id
LEFT OUTER JOIN
dbo.tbl_email e
ON
d.sd_e_id = e.e_id
WHERE
d.sd_renewal_date < GETDATE()+7;
If the cursor's only purpose is to send e-mail, then you probably want an
inner join against tbl_email. What is the point of getting rows where there
isn't a valid recipient?
I'd write the cursor for you, but it is entirely unclear to me how you are
deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
Please see http://www.aspfaq.com/5006
"Damon" <nonsense@.nononsense.com> wrote in message
news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
> Hi,
> I have a select statement which brings back several fields and several
> columns. Within one of these columns is an email address. I want to be
> able to cycle through each record in the select statement and email the
> details attached to their email address. My SP is below:-
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
> I know you can use the following to send emails:-
> @.sbj varchar(200),
> @.msg varchar(2000),
> @.recipient varchar(50)
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> but I want to incorporate that xp_sendmail with the select statement.
> If anyone has ever done this before and can give me some pointers it would
> be greatly appreciated.
> Thanks
> Damon
>|||Thank you very much for your reply. I can see why you are a SQL Server MVP.
Unfortunately my works have not supplied me with SQL training so have had to
learn myself, thus the messy code.
The @.sbj would be the same everytime, something like "List of renewal
dates". @.msg would literally be a compilation of the other fields i.e.
Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
being emailed can see the list of renewals that they have which are due up
within the next w
.
I really appreciate your help. Need to get on to my works to send me on a
SQL course.
Thanks again
Damon.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>I think you will need to use a cursor.
> Here's some help with your SELECT statement, by the way. It's still
> largely unreadable because of the superfluous prefixes and mismatched
> column names, but it should be a little easier to tackle. Note that I
> changed the WHERE clause to apply transformation to the constant, instead
> ofto the column, and kept with the tradition of object-operator-value
> instead of value-operator-object. getdate()>column+x is very difficult to
> process, at least for me. If the table is huge, you may find an advantage
> in declaring a variable of smalldatetime up front and calculating
> GETDATE()+7 and storing it in a constant. However, if there is no index
> on sd_renewal_date, it's probably all moot.
> SELECT
> Reg_No = s.s_id,
> URN = d.sd_urn,
> OFFICER = d.sd_reviewing_officer,
> RENEWAL_DATE = d.sd_renewal_date,
> OFFICER_EMAIL = e.e_officer_email
> FROM
> dbo.tbl_surveillance s
> INNER JOIN
> dbo.tbl_surveillance_dates d
> ON
> s.s_id = d.sd_s_id
> LEFT OUTER JOIN
> dbo.tbl_email e
> ON
> d.sd_e_id = e.e_id
> WHERE
> d.sd_renewal_date < GETDATE()+7;
> If the cursor's only purpose is to send e-mail, then you probably want an
> inner join against tbl_email. What is the point of getting rows where
> there isn't a valid recipient?
> I'd write the cursor for you, but it is entirely unclear to me how you are
> deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
> Please see http://www.aspfaq.com/5006
>
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
>|||I've taken an example cursor from Books Online and adjusted it somewhat to
fit your situation. However, it's just a rough draft and you will need to
complete it. Basically, the cursor allows you to iternate through the query
result one row at a time, giving you the ability to populate variables and
exec the xp_sendmail call. Every column that you plan to reference will need
to be assigned a variable. I've also added the FAST_FORWARD option so it
should use less resources.
DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
OPEN surveillance_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables should be in the same order as the columns in the
SELECT statement.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sbj = ?
select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
"Damon" <nonsense@.nononsense.com> wrote in message
news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
> Thank you very much for your reply. I can see why you are a SQL Server
> MVP. Unfortunately my works have not supplied me with SQL training so have
> had to learn myself, thus the messy code.
> The @.sbj would be the same everytime, something like "List of renewal
> dates". @.msg would literally be a compilation of the other fields i.e.
> Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
> being emailed can see the list of renewals that they have which are due up
> within the next w
.
> I really appreciate your help. Need to get on to my works to send me on a
> SQL course.
> Thanks again
> Damon.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||>> I have a select statement which brings back several fields and several co
lumns. <<
Interesting, since SQL does not have fields and columns are a totally
different concept.
More interesting, since SQL does not have records and rows are a
totally different concept. Tables are sets and not files;
sets by definition have no ordering, so cycles make no sense
whatsoever.
You need to use a cursor (explicit or hidden in a called procedure) to
convert the result table into a sequential structure that can have
loops. While you are catching up on the foundations of RM, you might
also want to learn ISO-11179 so that you stop using that silly "tbl-"
in your code, Standard SQL keywords, etc. You are writing SQL like a
procedural or OO programmer because you have not got the right mindset
yet.|||CELKO,
As I mentioned in my previous message, I have not had any official training
in SQL as my employer has not yet put me on a course, I have had to try and
learn this by myself so you will have to forgive my wording and code. I am
a VB programmer so most of the stuff I do I do in VB as I do not know SQL
very well and I find it easier to do in VB.
Thanks for your reply.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134664262.456898.41440@.g14g2000cwa.googlegroups.com...
> Interesting, since SQL does not have fields and columns are a totally
> different concept.
>
> More interesting, since SQL does not have records and rows are a
> totally different concept. Tables are sets and not files;
> sets by definition have no ordering, so cycles make no sense
> whatsoever.
> You need to use a cursor (explicit or hidden in a called procedure) to
> convert the result table into a sequential structure that can have
> loops. While you are catching up on the foundations of RM, you might
> also want to learn ISO-11179 so that you stop using that silly "tbl-"
> in your code, Standard SQL keywords, etc. You are writing SQL like a
> procedural or OO programmer because you have not got the right mindset
> yet.
>|||JT,
Thank you very much for your reply. I really appreciate your time in
replying in such detail.
I will have a crack @. this today.
Thanks again.
"JT" <someone@.microsoft.com> wrote in message
news:eNhERNZAGHA.2560@.TK2MSFTNGP12.phx.gbl...
> I've taken an example cursor from Books Online and adjusted it somewhat to
> fit your situation. However, it's just a rough draft and you will need to
> complete it. Basically, the cursor allows you to iternate through the
> query result one row at a time, giving you the ability to populate
> variables and exec the xp_sendmail call. Every column that you plan to
> reference will need to be assigned a variable. I've also added the
> FAST_FORWARD option so it should use less resources.
> DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
>
> OPEN surveillance_cursor
> -- Perform the first fetch and store the values in variables.
> -- Note: The variables should be in the same order as the columns in the
> SELECT statement.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sbj = ?
> select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
>
