將查詢設置為作為儲存過程執行並通過電子郵件發送結果

我有以下查詢：

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

我想將其設置為每天自動通過電子郵件發送值。如何將其轉換為儲存過程並使用。我正在使用 SQL Server 2008

1. 配置數據庫郵件
2. 創建一個 sql 代理作業以在 t-sql 下執行（您不需要儲存過程來滿足您的要求，但下面的程式碼可以輕鬆轉換為儲存過程 –> 我將由您決定 - 提示：看看對於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 = 'dbagroup@somecompany.com' --<-- 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. 為上述作業添加計劃。

引用自：https://dba.stackexchange.com/questions/104030