SQL Server procedure for INSERT filtering

Admin 1
Viewed 50

Is it possible to write a procedure in SQL Server that, before executing INSERT into a table, calls a function to check the format of email (1/0)?

INSERT will be executed only if the result of the checkemail function is 1, and in case of 0 it will return the error 'email is not valid'.

How it's done?

CheckEmail function:

CREATE FUNCTION dbo.CheckEmail
    (@Email VARCHAR(100))
RETURNS BIT 
AS
BEGIN 
    DECLARE @Result BIT

    IF @Email IS NOT NULL
    BEGIN   
        IF @Email LIKE '%["<>'']%'
           OR @Email NOT LIKE '%@%.%' 
           OR @Email LIKE '%..%'        
           OR @Email LIKE '%@%@%' 
           OR @Email LIKE '%.@%' 
           OR @Email LIKE '%@.%' 
           OR @Email LIKE '%.cm' 
           OR @Email LIKE '%.co' 
           OR @Email LIKE '%.OR' 
           OR @Email LIKE '%.ne' 
           OR @Email LIKE '@%' 

            SET @Result = 0;
        ELSE IF @Email LIKE '%_@__%.__%' 
                AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', @Email) = 0
            SET @Result = 1;
    END

    RETURN @Result;
END

Users table:

CREATE TABLE Users 
(
    "Id" UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), 
    "Email" VARCHAR(40) NOT NULL UNIQUE, 
    "Username" VARCHAR(30)  NOT NULL,
    "Password" VARCHAR(30) NOT NULL,
    "Name" NVARCHAR(50) NOT NULL,
    "Surname" NVARCHAR(50) NOT NULL
)
sql-server
3 Answers

You can use IF statement like that :

IF CheckEmail('my@email.com')
    INSERT INTO Users VALUES (...)

Also, you can add an ELSE statement if you want to do something with the line that doesn't correspond to what you want.

Documenation

user_109525030

There are several approaches, depending on your scenario, which you don't elaborate on. Assuming SQL Server given the SSMS tag.

If you are passing data to a stored procedure to handle a single insert, simply use if/then logic:

if dbo.CheckEmail(@email)=1
begin
  /* Insert data here */
end
else
begin
  /* raise any warnings / return error */
end

If you are inserting rows by selecting data from a source location, use a where criteria

insert into table Users (Email, Username, Password...)
select t1.Email, t1.Username, t1.Password...
from table t1
where dbo.CheckEmail(t1.email)=1

The most performant way would be to create a table-valued function instead of a scalar function which you can then use with cross apply

insert into table Users (Email, Username, Password...)
select t1.Email, t1.Username, t1.Password...
from table t1
cross apply dbo.CheckEmail(t1.email)ck
where ck.result=1
user_153326500

You are probably much better off doing this as a CHECK constraint

ALTER TABLE Users 
    ADD CONSTRAINT EmailCheck CHECK (
        NOT(  Email LIKE '%["<>'']%'
           OR Email NOT LIKE '%@%.%' 
           OR Email LIKE '%..%'        
           OR Email LIKE '%@%@%' 
           OR Email LIKE '%.@%' 
           OR Email LIKE '%@.%' 
           OR Email LIKE '%.cm' 
           OR Email LIKE '%.co' 
           OR Email LIKE '%.OR' 
           OR Email LIKE '%.ne' 
           OR Email LIKE '@%' 
        ) AND
        Email LIKE '%_@__%.__%' 
        AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', Email) = 0
    );
user_148689970
Related