July 15, 2009 at 8:37 am
I am trying to write a query that will email the individual responsible for a project(s) all the a/r for that project(s) that is over 60 days old. My thought was I was going to use xp_sendmail to send the email out to each individual, so I had to set up a cursor to step through each individual to fire off the email with the a/r. If I can do this without using a cursor, please enlighten me.
My problem is that my select statement to identify those individuals is correct - I get back 6 rows in my select statement. However, when I use that select statement in my cursor declaration, it doesn't seem to work because I get thousands of emails firing off. I removed the email part and just did a print and it would run forever if I let it. Can I not use my select statement in my cursor declaration? Thank you in advance for your assistance. Most articles dealing with cursors are just basically saying avoid them at all costs, so I couldn't find to much out there saying how to use them. Again, if I can do this without using a cursor, great. I couldn't think of how, personally. Thank you for your time.
use forefront
declare email_cursor cursor for
select distinct project_manager, '''' + rtrim(alpha_field) + '@worthandcompany.com' + '''' as email from
jc_job_master_mc (nolock)
inner join cr_open_item_mc (nolock)
on cr_open_item_mc.company_code = jc_job_master_mc.company_code and cr_open_item_mc.job_number = jc_job_master_mc.job_number
inner join pr_emp_user_fields_det_mc (nolock)
on jc_job_master_mc.company_code = pr_emp_user_fields_det_mc.company_code and
jc_job_master_mc.project_manager = ltrim(pr_emp_user_fields_det_mc.employee_code)
and user_def_sequence = '000068'
where cr_open_item_mc.company_code = 'wor'
and invoice_balance <> retention_balance
and datediff(day, invoice_date, current_timestamp) >= 60
declare @pm varchar(6), @email varchar(40)
open email_cursor
fetch next from email_cursor into @pm, @email
if (@@fetch_status = -1)
begin
print 'no overdue a/r'
close email_cursor
deallocate email_cursor
return
end
while (@@fetch_status = 0)
begin
PRINT @pm + @EMAIL
end
close email_cursor
deallocate email_cursor
July 15, 2009 at 8:39 am
Right before the end in the cursor loop you need to add another one of these.
fetch next from email_cursor into @pm, @email
while (@@fetch_status = 0)
begin
PRINT @pm + @EMAIL
fetch next from email_cursor into @pm, @email
end
otherwise it will keep looping and sending the same email.
July 15, 2009 at 8:41 am
That would make too much sense. That worked. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply