Return stored procedure result

TRY ALTERING YOUR LOAD.. PROCEDURE TO MORE LIKE THIS...

INVOKE AS
 DECLARE @BODY VARCHAR(8000),@ADDR VARCHAR(256)

 EXEC LOADaPTPATIENTINFO 1,2,'aPTemail'@BODY OUTPUT,@ADDR OUTPUT

 
ALTER PROCEDURE LoadAptPatientInfo
    @Patientid BIGINT ,
    @AppointmentID BIGINT
    ,@mode char(8) = 'None'
    ,@emailbody varchar(2000) Output
    ,@emailaDDR VARCHAR(256) oUTPUT

AS
    Set Nocount on
    declare @crlf char(2)
    set @crlf = char(10)+char(13)

    if @mode = 'None'
    begin
    SELECT  Patient.FirstName AS FirstName ,
            Patient.LastName AS LastName ,
            At.AppointmentType AS AptType ,
            CONVERT(VARCHAR(10), At.StartDate, 101) AS AptStartDate ,
            At.StartTime AS AptStartTime ,
            At.[STATUS] AS AptStatus ,
            At.IsRescheduled AS AptRescheduled ,
            At.IsAlarm AS AptAlarm ,
            At.IsRecurring AS AptRecurring ,
            At.Coments AS AptComments ,
            Ab.Email AS EmailAddress
    FROM    Appointment At
            INNER JOIN Patient ON At.PatientID = Patient.PatientID
            INNER JOIN dbo.AddressBook Ab ON dbo.Patient.AddressID = Ab.AddressID
    WHERE   Patient.PatientID = @PatientID
            AND At.AppointmentID = @AppointmentID ---AND At.[Status]='Confirmed'    
    ORDER BY    At.CreatedDt DESC
    end
    else if @mode = 'AptEmail'
    begin
         SELECT  TOP 1 @EMAILBODY='Dear '+Patient.FirstName +' '
            +Patient.LastName +','+@crlf
            +'Your appointment for '+At.AppointmentType +' '
            +'is scheduled at '+CONVERT(VARCHAR(10), At.StartDate, 101) +' '
            + At.StartTime
            @EMAILaDDR=Ab.Email
    FROM    Appointment At
            INNER JOIN Patient ON At.PatientID = Patient.PatientID
            INNER JOIN dbo.AddressBook Ab ON dbo.Patient.AddressID = Ab.AddressID
    WHERE   Patient.PatientID = @PatientID
            AND At.AppointmentID = @AppointmentID ---AND At.[Status]='Confirmed'    
    ORDER BY    At.CreatedDt DESC
    END
rETURN

HI,

What you can do is create a Temp table
#tmp_PatientInfo in  GetpatientEmailInformation sp.
Create temp table with same column and its datatypes as in select statement of LoadAptpatientinfo.

include the below statement
insert into #tmp_PatientInfo
Exec LoadAptPatientInfo

View below link for more info:
sqlddev.wordpress.com/2008/05/06/insert-into-temporary-table-from-stored-procedure/

You can define the email body in seperate table lets say i.e. EmailTemplate. In body column, use <CR1>, <CR2> and in email procedure replace those tag with the actual information like first name, last name etc.

thanks