store procedure check expirydate

[kumiko]

halo there, i need some help on writing store procedure.
i have 1 table which store the productid,productname,customerid expirydate of the product, remindersent.
customer table---> cusotmerid, customeremail
wat i wan to do is, i wan to check the expiry date for each customer 's products .
if the date diff between expirydate and today is 30days, then send a reminder to the customer and update the remindersent to 1. if the date diff expirydate and today is 14days, then send a reminder to the customer and update the remindersent to 2... how i write tis in store procedure?
plz guide me on how to write it. i know the basic for the store procedure, select, update, insert and delete only.thank you.hope can hear frm u all as soon.....coz urgent..plz help.

[studiokraft]

Hello,

What is the DB you are using, and what language have you programmed the front-end in?

If you are using PHP/MySQL you may want to check out:

DATE_SUB() in MySQL:
MySQL :: MySQL 5.0 Reference Manual :: 11.6 Date and Time Functions

strtotime() in PHP:
PHP: strtotime - Manual

[kumiko]

hi there, i m using MS SQL Server 2005 + ASP language

[Chiefos]

Im pretty sure that you can't send emails with SPRocs, I could be wrong but it doesn't sound like this would be the place to write this sort of logic. Seems that the best thing to do is to retrieve all customer information as one DB call and then process the email send out in your ASP code.

Depending on how many customer records you will have to update and the regularity of this type of action then its your call whether you batch these update statements together and run send them all over the wire to the db or not.