Setting query to run as a stored procedure and email the results 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
Configure Database Mail
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 -----&lt;--- 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 = 'dbagroup@somecompany.com' --&lt;-- CHANGE HERE 
    set @Mailsubject = 'Setting query to run as a stored procedure and email the results' --&lt;-- CHANGE HERE 
    set @tableHTML = N'&lt;H3&gt;&lt;FONT SIZE="3" FACE="Tahoma"&gt;Below is the report as per [http://dba.stackexchange.com/q/104030/8783] : &lt;/FONT&gt;&lt;/H3&gt;'
    set @tableHTML = @tableHTML + N'&lt;table border="1"&gt;' + N'&lt;FONT SIZE="2" FACE="Calibri"&gt;' + N'&lt;tr&gt;&lt;th align="center"&gt;New Connections&lt;/th&gt;' + N'&lt;th align="center"&gt;Domestic Metered&lt;/th&gt;' + N'&lt;th align="center"&gt;Commercial Metered Low&lt;/th&gt;' + N'&lt;th align="center"&gt;Commercial Metered High&lt;/th&gt;' + N'&lt;th align="center"&gt;Domestic Keypad&lt;/th&gt;' + N'&lt;th align="center"&gt;Generator&lt;/th&gt;' + N'&lt;th align="center"&gt;Commercial Keypad&lt;/th&gt;' + N'&lt;th align="center"&gt;Total Live&lt;/th&gt;' + N'&lt;/tr&gt;' + 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'&lt;/FONT&gt;' + N'&lt;/table&gt;';
    set @tableHTML = @tableHTML + N'&lt;H1&gt;&lt;FONT SIZE="1" FACE="Verdana"&gt;Report By: @TheRockStarDBA ;Generated On: ' + convert(varchar, getdate()) + '&lt;/FONT&gt;&lt;/H1&gt;'

    --print @tableHTML
    exec msdb.dbo.sp_send_dbmail    @profile_name = 'YOUR MAIL PROFILE'----&lt; CHANGE HERE !
                                   ,@recipients = @mail_recipients
                                    ,@subject = @Mailsubject
                                    ,@body = @tableHTML
                                    ,@body_format = 'HTML';
end

Add a schedule to the above job.