stored procedure that sends an email alert after an invalid character is inserted into a specific co-CodePudding

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

CodePudding user response：

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.