Some applications have batch jobs that send emails to users based on some criteria. There is no user interface to this, so a stored procedures that run as schedule jobs in SQL Server 2005 works well. An example of this is a reminder feature of your application. Maybe you want to remind all users to enter their time sheets on Friday afternoons. Below is an overview of how to write the sql for the lines in the stored procedure that can be used in the schedule job.
For more information on using a cursor see my blog entry at: http://justgeeks.blogspot.com/2008/08/using-t-sql-cursor-in-sql-server.html For more informaiton on configuring SQL Server Database Mail check out http://www.mssqltips.com/tip.asp?tip=1100 or do a search on Google for something like: setting up database mail. There are lots of articles on the subject. Please note that this code will only work on SQL 2005 (and maybe newer version, but definitely not SQL 2000). There is a lot of options for sp_send_dbmail. I recommend checking out the parameters, etc if you need more information. See http://msdn.microsoft.com/en-us/library/ms190307.aspx Here is the code to send email to a selection of people.
Declare @FName as nvarchar(50) Declare @LName as nvarchar(100) Declare @Email as nvarchar(255) Declare @MySubject as nvarchar(128) Declare @Message as nvarchar(MAX) Declare PersonCursor CURSOR FAST_FORWARD FOR Select fname, lname, email from Person where city = 'Phoenix' order by fname OPEN PersonCursor FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email WHILE @@FETCH_STATUS = 0 BEGIN -- send the email Set @Message = 'This is a test email.' Set @MySubject = 'Hi ' + @FName + ' ' + @FName EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MyProfile', @recipients = @Email, @subject = @MySubject, @body = @Message FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email END CLOSE PersonCursor DEALLOCATE PersonCursor