CODEWITHSUNDEEP
asp.netsql
Sana Joseph
Sana Joseph

Reputation: 1948

How to prevent data from being added if it already exists in database

I want the user to register in a website & if his email already exists his data shouldn't be added.

Now this is the function I use: 

IF exists( SELECT        SU_ID, SU_Email
           FROM            SystemUser
           WHERE        (SU_Email = @email)
           )
Begin
    return 0
END
else
begin
    return -1
end

but even when the select statement returns a value it doesn't return 0 & returns -1. What am I doing wrong ?

Upvotes: 0

Views: 540

Answers (4)

Sanjay Gupta
Sanjay Gupta

Reputation: 186

Define Unique as constraint in SQL Server datacolumn. Handle exception for the same.

Upvotes: 1

Henry
Henry

Reputation: 1008

try this

DECLARE @FOUND INT
SET @FOUND = (SELECT COUNT(*) AS F FROM SystemUser WHERE (SU_Email = @email))
IF @FOUND > 0 THEN
 RETURN 0
ELSE
 RETURN -1

Upvotes: 2

Waqar Janjua
Waqar Janjua

Reputation: 6123

When we write a function we also declare a return variable, you are missing that, Follow the below code

Create function CheckUser(@Email @Varchar(50))
RETURNS Bit
DECLARE @ans bit  -- Declare a bit variable

IF exists( SELECT        SU_ID, SU_Email
       FROM            SystemUser
       WHERE        (SU_Email = @email)
       )
    Begin
       set @ans = 1
    END
else
    Begin
       set @ans = 0
    End

RETURNS @ans

Upvotes: 1

Amir Ismail
Amir Ismail

Reputation: 3883

Try to insert if not exists

 IF Not exists( SELECT * FROM  SystemUser  WHERE SU_Email = @email)
 Begin
     --Insert statment here
 END

Upvotes: 1

Related Questions