1

I have the following query:

select COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections'
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered'
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low'
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High'
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad'
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator'
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad'
, COUNT(*) as 'Total Live'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where New_AccountStage = 7
and AddressTypeCode is null

I want to set this up to automatically email the values on a daily basis. How can I convert this into a stored procedure and use. I am using SQL server 2008

0

1 Answer 1

2
  1. Configure Database Mail

  2. Create a sql agent job to run below t-sql (you dont need a stored procedure for what you are asking, but below code can be easily converted into a stored procedure --> I will leave that up to you - hint: just look for Create procedure as ..)

    ---- first check if there are any rows returned ... as we dont want to send empty email ....
    if exists (
            select 1
            from AccountExtensionBase as a
            inner join CustomerAddressBase as b on a.AccountId = b.ParentId
            where New_AccountStage = 7
                and AddressTypeCode is null
            )
    begin
        if object_id('#final_results') is not null
            drop table dbo.#final_results;
    
        select COUNT(case 
                    when New_accounttype = 1
                        then New_AccountType
                    else null
                    end) as 'New Connections'
            ,COUNT(case 
                    when New_accounttype = 2
                        then New_AccountType
                    else null
                    end) as 'Domestic Metered'
            ,COUNT(case 
                    when New_accounttype = 3
                        then New_AccountType
                    else null
                    end) as 'Commercial Metered Low'
            ,COUNT(case 
                    when New_accounttype = 4
                        then New_AccountType
                    else null
                    end) as 'Commerical Metered High'
            ,COUNT(case 
                    when New_accounttype = 5
                        then New_AccountType
                    else null
                    end) as 'Domestic Keypad'
            ,COUNT(case 
                    when New_accounttype = 6
                        then New_AccountType
                    else null
                    end) as 'Generator'
            ,COUNT(case 
                    when New_accounttype = 7
                        then New_AccountType
                    else null
                    end) as 'Commercial Keypad'
            ,COUNT(*) as 'Total Live'
        into dbo.#final_results -----<--- put results into temp table for easy processing ...........
        from AccountExtensionBase as a
        inner join CustomerAddressBase as b on a.AccountId = b.ParentId
        where New_AccountStage = 7
            and AddressTypeCode is null
    
        --- prepare for sending email ... since we have something to send ....
        declare @tableHTML nvarchar(MAX)
            ,@tableHTML2 nvarchar(MAX)
            ,@tableConnErr nvarchar(MAX)
            ,@tableDBConnErr nvarchar(MAX);
        declare @Mailsubject nvarchar(255)
            ,@mail_recipients nvarchar(255)
    
        set @mail_recipients = '[email protected]' --<-- CHANGE HERE 
        set @Mailsubject = 'Setting query to run as a stored procedure and email the results' --<-- CHANGE HERE 
        set @tableHTML = N'<H3><FONT SIZE="3" FACE="Tahoma">Below is the report as per [http://dba.stackexchange.com/q/104030/8783] : </FONT></H3>'
        set @tableHTML = @tableHTML + N'<table border="1">' + N'<FONT SIZE="2" FACE="Calibri">' + N'<tr><th align="center">New Connections</th>' + N'<th align="center">Domestic Metered</th>' + N'<th align="center">Commercial Metered Low</th>' + N'<th align="center">Commercial Metered High</th>' + N'<th align="center">Domestic Keypad</th>' + N'<th align="center">Generator</th>' + N'<th align="center">Commercial Keypad</th>' + N'<th align="center">Total Live</th>' + N'</tr>' + ISNULL(CAST((
                        select td = [New Connections],''
                            ,td = [Domestic Metered],''
                            ,td = [Commercial Metered Low],''
                            ,td = [Commercial Metered High],''
                            ,td = [Domestic Keypad] ,''
                            ,td = [Generator],''
                            ,td = [Commercial Keypad],''
                            ,td = [Total Live],''
                        from (
                            select *
                            from dbo.#final_results
                            ) A
                        order by 1 asc
                        for xml PATH('tr')
                            ,TYPE
                        ) as nvarchar(MAX)), '')
        set @tableHTML = @tableHTML + N'</FONT>' + N'</table>';
        set @tableHTML = @tableHTML + N'<H1><FONT SIZE="1" FACE="Verdana">Report By: @TheRockStarDBA ;Generated On: ' + convert(varchar, getdate()) + '</FONT></H1>'
    
        --print @tableHTML
        exec msdb.dbo.sp_send_dbmail    @profile_name = 'YOUR MAIL PROFILE'----< CHANGE HERE !
                                       ,@recipients = @mail_recipients
                                        ,@subject = @Mailsubject
                                        ,@body = @tableHTML
                                        ,@body_format = 'HTML';
    end
    
  3. Add a schedule to the above job.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.