SQL Server procedure for INSERT filtering-CodePudding

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
)

CodePudding user response：

You can use IF statement like that :

IF CheckEmail('[email protected]')
    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

CodePudding user response：

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