none
how to Capture query result in a variable?

    Question

  • hi i have written a store proc to send a e-mail after my package is executed For the body of the mail i have another store proc called which give a result set as a table and that has to be displayed in the mail . the hilighted part of the store proc is where i have to do some thing for this to happen below is my store procs  

    Any help is greatly appreciated !!??

    ***************This is the main Store proc****************

     

    ALTER Procedure [dbo].[Send_Email_Summary_DART_File_Download]

     

    @Status INT

     

     AS

     BEGIN

     

    /**** Declare the variables used by SP internally ****/

     

    DECLARE @DMRbody NVARCHAR(max);

    DECLARE @DMRrecipients NVARCHAR(500);

    DECLARE @DMRSubject NVARCHAR(500); 

    DECLARE @DMRimportance NVARCHAR(500);

    -- DECLARE @FileSize INT ;

    -- DECLARE @FileId INT

    --

     

    /**** Check If @status = 0 then send E-mail saying job completed with no errors

    If @status = 1 then send E-mail saying job completed with errors   ****/

    IF @Status = 0 

     

    BEGIN

    SET @DMRbody = 'DMR Dart Files Downloaded to specified path without errors'; 

    SET @DMRbody = @DMRbody + Char(13);

    SET @DMRbody = @DMRbody + Char(10);

    SET @DMRbody = @DMRbody + 'Enter Into Newline';

    Print @DMRbody

    Exec Get_Mail_Body_MSG_For_DMRDownload

     

    Exec Get_Mail_Header @DMRSubject OUTPUT

    SET @DMRimportance = 'NORMAL'

    END

    ELSE 

    BEGIN

    SET @DMRSubject = 'DMR Dart Files Downloaded to specified path with errors'

    SET @DMRbody = 'DMR Dart Files Downloaded to specified path with errors'

    SET @DMRimportance = 'HIGH'

    END

    END 

     

     

     

    /**** Set the Recipients to whome the mails has to be sent  ****/

     

    SET @DMRrecipients = 'abc@xyz.com'

    --

    --/**** Set the Recipients to whome the mails has to be sent  ****/

    --

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SQL 02 DatabaseMail',

    @recipients=@DMRrecipients,

    @subject=@DMRsubject,

    @body=@DMRbody,

    @body_format=HTML,

    @importance=@DMRimportance

     

     

     

     

     

    ******************this is for the body of the mail**************************

     

     

    ALTER PROCEDURE [dbo].[Get_Mail_Body_MSG_For_DMRDownload] 

    AS

    BEGIN

    Declare @Body Table

    (

    FileId int,

    FileSize int,

    FileName nvarchar(50),

    Status nvarchar(50),

    StartDate Datetime,

    EndDate DateTime)

     

    insert into @Body 

    select FileId,FileSize,FileName,Status,StartDate,EndDate from dbo.DART_Files_To_Download

    where ( DAY(StartDate) = DAY(GETDATE())

    AND

    MONTH(StartDate) = MONTH(GETDATE())

    AND

    YEAR(StartDate) = YEAR(GETDATE())

    )

    select * from  @Body

    END


     

     

    Wednesday, October 20, 2010 6:16 PM

Answers

  • You can use INSERT EXEC to capture the result into a table.

    If the result set includes multiple lines, you can make it one string with FOR XML PATH(''):

    SELECT name + ',' FROM sys.objects FOR XML PATH('')

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by KJian_ Tuesday, October 26, 2010 7:38 AM
    Wednesday, October 20, 2010 10:01 PM