sql-server - SQL Server：检查不正确数据并在发现时发送电子邮件的存储过程

Question

我正在研究一个检查无效年份格式的存储过程 - '0YYY' 或 '00YY' 0r '000Y' - 如果找到，存储过程会发送一封包含结果的电子邮件。

存储过程现在可以工作，但我需要进行一些更改。我需要存储过程只发送名称和不正确的日期并隐藏正确的日期，如下所示。

name       Start_date     end_date
--------------------------------------
John       0019-12-01
Jane                       0020-01-05
Mike       0019-01-05      0019-12-01 

存储过程现在看起来像这样。

Declare @count int

select @count = count(*)
from [Assign_Tbl] a
where (a.Assign_Thru_Da >= getdate() or a.Assign_Thru_Da is null 
       or (a.Assign_From_Da >= getdate() and a.Assign_Thru_Da >= getdate())
       or (a.Assign_From_Da <= getdate() and a.Assign_Thru_Da <= getdate())) 
  and ((a.Assign_From_Da like '0%' or a.Assign_From_Da like '00%' or a.Assign_From_Da like '000%')  
       or (a.Assign_Thru_Da like '0%' or a.Assign_Thru_Da like '00%' or a.Assign_Thru_Da like '000%'))

if @count > 0
begin
    exec msdb.dbo.sp_send_dbmail
        --@profile_name='emailFrsdata2',
        @recipients=N'[e-mail]',
        @subject = 'WFTS - Incorrect Assignment Dates found',
        @query = N'select r.RscMaster_Name_Ch as Name,  a.assign_from_da as Start_Date, a.Assign_Thru_Da as End_Date
                   from [Assign_Tbl] a
                   left join [Resource_Master_Tbl] r on r.RscMaster_No_In = a.RscMaster_No_In
                   where (a.Assign_Thru_Da >= getdate() or a.Assign_Thru_Da is null 
                          or (a.Assign_From_Da >= getdate() and a.Assign_Thru_Da >= getdate())
                          or (a.Assign_From_Da <= getdate() and a.Assign_Thru_Da <= getdate())) 
                     and ((a.Assign_From_Da like ''0%'' or a.Assign_From_Da like ''00%'' or a.Assign_From_Da like ''000%'')  
                          or (a.Assign_Thru_Da like ''0%'' or a.Assign_Thru_Da like ''00%'' or a.Assign_Thru_Da like ''000%''))
                   order by r.RscMaster_Name_Ch',
    @attach_query_result_as_file = 0
end

标签： sql-serverstored-procedures