November 13, 2003 at 6:48 am
I'm not sure of the best way to do this so I figured I'd ask here.
Essentially, I need to check the expiration dates of all reps and based on how close they are to the expiration date send them an email.
Ex.
Rep1 expiration date is 12/13 which is 30 days away so I send expirationmessage 1
Rep2 expiration date is 1/13 which is 60 days away so I send expirationmessage 2
etc.
So far i'm creating a table with reps who will need the same expirationmessage and i'd like to send this table to a stored proc to actually send this message.
Unfortunately, i'm lost as to how to do this. Currently i've got this
declare @EO90Reps table(email nvarchar(50))
insert into @EO90Reps
select email from reps where dateterminated is null and (datediff(dd,eoexpirationdate, getdate()) >= 90)
exec JOB_EOExpirationMail00 @EO90Reps
I keep getting an error that I haven't declared @EO90Reps
Is there a better way to do this?
November 13, 2003 at 7:15 am
Table data type can't be used as a parameter in stored procedures!
you will have to create a loop to cyle through the table to either send an message per rep or better yet concatenate all reps email addresses separating them by semicolon
and send a unique email with the recpientlist built as explained above
HTH
* Noel
November 13, 2003 at 7:44 am
Use a temporary table instead of a table variable.
--Jonathan
--Jonathan
November 13, 2003 at 8:54 am
This actually sounds like a good idea! all I have to do now is work on creating the loop. Anybody want to provide a code sample?
quote:
Table data type can't be used as a parameter in stored procedures!you will have to create a loop to cyle through the table to either send an message per rep or better yet concatenate all reps email addresses separating them by semicolon
and send a unique email with the recpientlist built as explained above
HTH
November 14, 2003 at 6:50 am
Ok so I decided to that putting all the addresses in one variable and sending that to xp_mail would be my best bet. Here's how i'm trying to do it:
declare @r nvarchar(25)
declare @mail nvarchar(250)
declare reps_cursor cursor for
select email from reps where dateterminated is null and (datediff(dd,eoexpirationdate, getdate()) > 90)
open reps_cursor
fetch next from reps_cursor
while (@@fetch_status =0)
Begin
set @mail = @mail + @r + ';'
fetch next from reps_cursor into @r
end
close reps_cursor
deallocate reps_cursor
select @mail
but it's only going as far as the first record. Am I doing something wrong?
Edited by - thundr51 on 11/14/2003 06:55:34 AM
November 14, 2003 at 7:06 am
Your query will result in null because you have not initialized @mail, use
set @mail = ''
or simply do this
set @mail = ''
select @mail = @mail + email + ';' from reps
where dateterminated is null
and (datediff(dd,eoexpirationdate, getdate()) > 90)
Edited by - davidburrows on 11/14/2003 07:07:55 AM
Far away is close at hand in the images of elsewhere.
Anon.
November 14, 2003 at 7:18 am
quote:
Your query will result in null because you have not initialized @mail, useset @mail = ''or simply do this
set @mail = ''
select @mail = @mail + email + ';' from reps
where dateterminated is null
and (datediff(dd,eoexpirationdate, getdate()) > 90)
Or, even more simply (with no trailing delimiter and no need to initialize the variable):
SELECT @mail = ISNULL(@mail + ';','') + Email
FROM Reps
WHERE DateTerminated IS NULL
AND (DATEDIFF(dd,EoExpirationDate,GETDATE()) > 90)
Although, as your called SP obviously was written to work with the values in a table, I don't know why you don't just use a temporary table; do you now have to parse this string in the called SP?
--Jonathan
--Jonathan
November 14, 2003 at 7:25 am
Nice one Jonathan, will have to remember that one
Far away is close at hand in the images of elsewhere.
Anon.
November 14, 2003 at 7:26 am
At this point my query isn't really returning anything...i think
At this very moment i'm running this query on Query Analyzer and it STILL hasn't return yet.
I thought that maybe initializing the @mail was the cause but that doesn't seem to be it.
quote:
Your query will result in null because you have not initialized @mail, useset @mail = ''or simply do this
set @mail = ''
select @mail = @mail + email + ';' from reps
where dateterminated is null
and (datediff(dd,eoexpirationdate, getdate()) > 90)
Edited by - davidburrows on 11/14/2003 07:07:55 AM
November 14, 2003 at 7:36 am
I suspect that as exipration date is in the future, your datediff will result in a negative number and never be > 90.
Try swapping the dates around:
datediff(dd,getdate(),eoexpirationdate) > 90)
Jeremy
November 14, 2003 at 8:09 am
Actually you're correct but I was still getting results (26 to be exact) but at this point that wasn't my problem.
Here's what i've got now
declare @r nvarchar(25)
declare @mail nvarchar(250)
set @mail = ' '
set @r = ' '
declare reps_cursor cursor for
select email from reps
where dateterminated is null and (datediff(dd,getdate(),eoexpirationdate) >= 0)
and (datediff(dd,getdate(),eoexpirationdate) <= 29) and eoexpirationdate > getdate()
and email is not null
open reps_cursor
fetch next from reps_cursor into @r
while (@@fetch_status =0)
set @mail = @mail + @r + ';'
fetch next from reps_cursor into @r
close reps_cursor
deallocate reps_cursor
select @mail
everything looks ok to me yet this query has been running for over 7 mins and is STILL GOING!
my cursor select is returning 3 records so I know it shouldn't take that long to concat three emails...
I believe the problem lies in my set statement
set @mail = @mail + @r + ';'
If I take it out everything kinda works (not the result i'm looking for but still is the correct/expected results). Is it not possible to set a variable in a while loop??
quote:
I suspect that as exipration date is in the future, your datediff will result in a negative number and never be > 90.Try swapping the dates around:
datediff(dd,getdate(),eoexpirationdate) > 90)
Jeremy
November 14, 2003 at 8:18 am
It might be the while clause.
open reps_cursor
fetch next from reps_cursor into @r
while (@@fetch_status =0) begin
set @mail = @mail + @r + ';'
fetch next from reps_cursor into @r
end
close reps_cursor
deallocate reps_cursor
select @mail
I think all it is doing is looping round set @mail = @mail + @r + ';'
Jeremy
November 14, 2003 at 8:30 am
That worked!
I don't quite understand why though
I expected that I needed
to loop around the set statement.
I was thinking along the lines of
a regular 'while' like in VB or C#
where the last fetch was like a 'end while'
I shall have to be more careful next time.
Thanx again!
quote:
It might be the while clause.open reps_cursor
fetch next from reps_cursor into @r
while (@@fetch_status =0) begin
set @mail = @mail + @r + ';'
fetch next from reps_cursor into @r
end
close reps_cursor
deallocate reps_cursor
select @mail
I think all it is doing is looping round set @mail = @mail + @r + ';'
Jeremy
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy