将无效字符插入特定列后发送电子邮件警报的存储过程

发布于 2025-02-10 13:11:49 字数 671 浏览 1 评论 0原文

我正在尝试创建一个存储过程，该过程将在无效字符插入特定列时向我发送电子邮件。我已经想出了一些脚本来创建它，但是我认为我缺少一些东西，因为它不断给我错误。我还已经确定了无效的角色是重音角色，因此我尝试使用它，因此当输入该字符时，我会收到通知。

CREATE PROCEDURE InvalidCharacterCheck
AS
IF ((SELECT * FROM StudentNames WHERE LastName like '%`%') like '%' + '`' + '%')
BEGIN
    EXEC msdb.dbo.sp_send_dbmail 
         @recipients = '[email protected]',
         @subject = 'The LastName column has a record with the grave accent invalid character'
END
ELSE
BEGIN
    PRINT 'There is no invalid character in the LastName column at present'
END

原文

I am trying to create a stored procedure that will send an email to me when or after an invalid character is inserted into a particular column. I've already come up with some of the script to create it but I think I am missing something as it keeps giving me errors. I also already identified the invalid character to be the Grave Accent character so I've tried to use that so when that character is input I get notified.

CREATE PROCEDURE InvalidCharacterCheck
AS
IF ((SELECT * FROM StudentNames WHERE LastName like '%`%') like '%' + '`' + '%')
BEGIN
    EXEC msdb.dbo.sp_send_dbmail 
         @recipients = '[email protected]',
         @subject = 'The LastName column has a record with the grave accent invalid character'
END
ELSE
BEGIN
    PRINT 'There is no invalid character in the LastName column at present'
END

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

治碍 2025-02-17 13:11:49

您可以尝试这样的事情：

CREATE PROCEDURE TestProcedure
    AS
BEGIN

DECLARE @errorCount INT;

SELECT
    @errorCount = COUNT(FirstName)
FROM
    StudentNames
WHERE
    FirstName like '%`%'
    OR LastName like '%`%'

IF (@errorCount > 0) 
BEGIN 
    EXEC msdb.dbo.sp_send_dbmail @recipients = '[email protected]',
    @subject = 'The LastName column has a record with the grave accent invalid character' 
END 
ELSE
BEGIN 
    PRINT 'There is no invalid character in the LastName column at present' 
END
END
GO

但是，我会考虑预防而不是治愈，并在将数据插入表格之前对数据进行消毒。您不想清理一团糟，只是不要首先创建它。

You could try something like this:

CREATE PROCEDURE TestProcedure
    AS
BEGIN

DECLARE @errorCount INT;

SELECT
    @errorCount = COUNT(FirstName)
FROM
    StudentNames
WHERE
    FirstName like '%`%'
    OR LastName like '%`%'

IF (@errorCount > 0) 
BEGIN 
    EXEC msdb.dbo.sp_send_dbmail @recipients = '[email protected]',
    @subject = 'The LastName column has a record with the grave accent invalid character' 
END 
ELSE
BEGIN 
    PRINT 'There is no invalid character in the LastName column at present' 
END
END
GO

However I would look at prevention rather than cure, and sanitise the data before it gets inserted into the table. You dont want to have to clean up a mess, just dont create it in the first place.

回复收藏 0 原文

~没有更多了~