asked on

How to include table data in a stored procedure email body...?

I have the below procedure (well, you see it as the alter but same thing)...what I need to do is for the procedure to scan the table and grab the name and job number and expiry date of any record that has an expiry approach within 15 days. Then I need to be able to list that information out in the body of the email like this:

The following jobs have expiring dates:

Name: Billy Bob
Job Number: 15487
Expiring: 8/15/2013

Name: James Joyce
Job Number: 65148
Expiring: 8/22/2013

Here's what the basic procedure format...

ALTER PROCEDURE [dbo].[p_send_altermail]

AS

-- SELECT STATEMENT HERE TO PULL NAME, JOB NUMBER, EXPIRY DATE IF WITHIN 15 DAYS

BEGIN
EXEC msdb..sp_send_dbmail @profile_name='mssjobbook',
@recipients='mail@place.com',
@subject='MSS Job Book Notification',
@body= ' The following jobs have a date expiring soon... ' ;

END

Thanks! (sql server 2005, using database mail)

Steve Wales

Do something like this (I did something similar not to long ago):

(Only partial code here but you'll get the picture)

  Declare @CRLF    char(2);
  Declare @String1 nvarchar(max);
  SELECT @CRLF=CHAR(13)+CHAR(10);

    BEGIN
      Select @String1 = 'Work Order #'+@wono+' linked to work request #'+@rqstid+' was recently closed.'+@CRLF
                     + 'Description: '+@wdesc+@CRLF+@CRLF
                     + 'Your linked work request #'+@rqstid+' has been automatically closed.'+@CRLF
                     + 'This email is being sent to let you know that the work has been completed.';
      exec msdb.dbo.sp_send_dbmail 
           @profile_name = 'My Mail Profile'
          ,@recipients = @emladr
          ,@subject = 'Your work request has been closed'
          ,@body = @String1
    END

Open in new window

Kevin Smith

ASKER

Okay...so where is the actual select statement? I guess I'm looking something that's showing a select/from/where statement...

ASKER CERTIFIED SOLUTION

Steve Wales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Smith

ASKER

sorry, got on another project...gonna play with this again tomorrow...