Solved

Return stored procedure result

Posted on 2010-11-29
5
273 Views
Last Modified: 2012-05-10
Hi,

I have made one SP which sends an email. The email is sent successfully. In the email body i wish to sent an email something like

Dear <Patient FirstName> <Patient LastName>,

Your Appointment for <Checkup> is scheduled at <Date & Time>


Ignore the italic font, all the detail under '< >' comes from another stored procedure.


The SP that contains query to send email is attached as "Email-SP.txt". The Patient Information containing query is attached as "Patient-Info.txt". A sample result set is attached as an image.

My Question: How to bring the patient information (Patient-Info.txt) so that email with above format is sent up?

Let me know for any doubts.

Email-SP.txt
Patient-Info.txt
Patient-ResultSet.png
0
Comment
Question by:meispisces
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34229242
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
0
 
LVL 3

Expert Comment

by:GSGDBA
ID: 34229482
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/
0
 
LVL 3

Accepted Solution

by:
GSGDBA earned 500 total points
ID: 34229494
HI,

I missed one more step here.
It's like this:
1) Create temp table
2) Insert into temp table exec SP
3) slect * from Temp table.
4) drop temp table.
that's it

0
 
LVL 1

Expert Comment

by:amu_27
ID: 34229919
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.
0
 
LVL 7

Author Closing Comment

by:meispisces
ID: 34368422
thanks
0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question