Create A Stored Procedure that will send an email to ALL the people in a database with their contacts.

I am working with SQL Server 2000 and have the following Views created:

1. "vw_active_group_providers" - this view contains the following fields and data

PROVIDER_ID          FIRST_NAME          LAST_NAME          EMAIL1
128                           John                       Adams                 jadams@testemail1.com
544                           Abraham                Lincoln                 abelincoln@testemail2.com
and so on...(thousands of rows)

2. "vw_home_phone" - this view contains the following fields and data

PROVIDER_ID          FIRST_NAME           LAST_NAME           HOME         PHONE_NUMBER
128                           John                       Adams                  home          (000) 000-0000
544                           Abraham                Lincoln                  home          (111) 111-1111
and so on...(thousands of rows)

3. "vw_cell_phones" - - this view contains the following fields and data

PROVIDER_ID          FIRST_NAME           LAST_NAME           CELL         PHONE_NUMBER
128                           John                       Adams                  cell           (000) 000-0000
544                           Abraham                Lincoln                  cell           (111) 111-1111
and so on...(thousands of rows)

4. "vw_work/office" - - this view contains the following fields and data

PROVIDER_ID          FIRST_NAME           LAST_NAME           WORK/OFFICE         PHONE_NUMBER
128                           John                       Adams                  office                       (000) 000-0000
544                           Abraham                Lincoln                  office                       (111) 111-1111
and so on...(thousands of rows)

5. "vw_home_address" - - this view contains the following fields and data

PROVIDER_ID    ADDRESS_TYPE    ADDRESS_LINE1        ADDRESS_LINE2    ADDRESS_CITY     ADDRESS_STATE   ADDRESS_POSTAL_CODE
128                           home            101 Mountain Rd        NULL                       Cleveland             OH                        55432
544                           home            202 Crimson St           Apt # 343               Dallas                   TX                         75070  
and so on...(thousands of rows)

PLEASE NOTE:
 - "vw_home_address" does not have names
 - PROVIDER_ID is the only field that connects all five of the above views.


Here's what I want to achieve:

I want to create a Stored Procedure that looks at the above views and when I execute it, it should send an email to everyone in the "vw_active_group_providers" view.

The people getting the email should see the following in their email EXACTLY:

------------------------------------------------

SUBJECT: Contact Information Verification

BODY:

As part of our ongoing effort to keep your contact information updated at all times, we request you to please verify your information below.

If your contact information has changed or been updated, please click "UPDATE CONTACT INFORMATION" link to email us with your updates.

The following contact information is registered with us:

LAST NAME, FIRST NAME
ADDRESS_LINE1
ADDRESS_LINE2 <----------//Should not display if NULL//
ADDRESS_CITY
ADDRESS_POSTAL_CODE

HOME PHONE: PHONE_NUMBER
CELL PHONE:   PHONE_NUMBER
OFFICE PHONE: PHONE_NUMBER  <----------//Should not display if NULL//

UPDATE CONTACT INFORMATION <-----------//This should be a mailto link that when clicked on sends an email to corp@corporation.com with the subject line "Please Update My Contact Information" allowing the person to send an email with any updates to his/her contacts//

Thank you,
CORP Notification System
--------------------------------------------------

So gurus, can you help me create a stored procedure to do this? And if so, how can I test it first to make sure it works?

I really appreciate it.

Thanks.