Post reply

Replacing Blank Report with some default message

  • June 2, 2011 at 1:06 am

    #238515

    Hi,

    I have configured Database mail for some reports on my database. The issue I have got is when there is no data to be retrieved for at that particular time . I receive a blank report. What I want is when there is no data for that particular report at that particular time the Report should have some message such as " No Searches for clients for this period" rather than receiving a blank report. How can I do this?

    I have pasted my SP for generating the mail for reference.

    CREATE PROCEDURE [dbo].[USP_SendMemberActivityReport]

    @Member_Id INT,

    @Email VARCHAR(50)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @tableHTML NVARCHAR(MAX) ;

    SET @tableHTML =

    N'<H4><font face="CALIBRI"> CLIENT ACTIVITY REPORT FOR '+LEFT(GETDATE(),12)+' TILL '+RIGHT(DATEADD(HH,0,GETDATE()),7)+' </H4>' +

    N'<table border 0.5px BORDERCOLOR="Black" width="90%" cellpadding="0" cellspacing="0" style="font-family:calibri; font-size:15" >' +

    N'<tr style="color:black; background-color:#BDBDBD;"><th colspan="10"><b>SEARCH COUNT, BOOKED COUNT, TICKETED COUNT & EXCEPTION COUNT FOR CLIENTS</b></th></tr>' +

    N'<tr><th colspan="10" align="LEFT"><b>DATE: '+LEFT(GETDATE(),12)+' TIME INTERVAL: 12:00AM TO '+RIGHT(DATEADD(HH,0,GETDATE()),7)+'</b></th></tr>' +

    N'<tr style="color:white; background-color:#CC0000;"><td align="center">ClientID</td><td align="center" width="15%">Client Name</td><td align="center">Internal Search Count</td><td align="center">External Search Count</td><td align="center">Total Search Count</td> </tr>' +

    CAST ( ( SELECT td=ClientId, '',

    td=ClientName, '',

    td=ISNULL(IntSearchCount,0), '',

    td=ISNULL(ExtSearchCount,0), '',

    td=ISNULL(TotSearchCt,0) , '',

    FROM ClientSearch

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail @recipients=@Email,

    @profile_name = 'Admin',

    @subject = 'CLIENT ACTIVITY REPORT ',

    @body = @tableHTML,

    @body_format = 'HTML' ;

    END

    Regards,

    Nithin

  • June 2, 2011 at 2:19 am

    #1332946

    i dont have the code, but logic can be used as :-

    Count the number of records being returned by query, if its 0 then :-

    print :- "No records to display"

    else

    do whatever you doing now.

    ----------
    Ashish

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply