CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Masriyah
Masriyah

Reputation: 2525

Stored procedure to return a bool

I am creating a stored procedure that checks if the email the user is saving (edit profile email) to check if the new email already exists in the database for a different user or not. If the email entered doesn't exist in database then safe to use and save but if it exists in database and the user id and name don't match the user id and name of the person saving meaning the new email belongs to a different user return false which i will use to throw and error message

here is what i have:

create procedure CheckDuplicate
@email nvarchar(256),
@Id int
As
Begin
set nocount on;
select usr.Id, usr.UserName, usr.Email
from Users usr
where usr.Email = @email and usr.Id = @Id
end

Question: how can i add bool to check if it exists or not in the database?

Upvotes: 2

Views: 3758

Answers (3)

HABO
HABO

Reputation: 15852

This will return 1 if another user has the requested email address, otherwise 0:

create procedure CheckDuplicate
  @email nvarchar(256),
  @Id int
As
  set nocount on;
  select case
    when exists ( select 42 from Users where Email = @email and Id <> @Id ) then Cast( 1 as Bit )
    else Cast( 0 as Bit ) end as EmailBelongsToAnotherUser

Alternatively, you could return the result in an output parameter:

create procedure CheckDuplicate
  @email nvarchar(256),
  @Id int,
  @EmailCollision as Bit Output
As
  set nocount on;
  select @EmailCollision = case
    when exists ( select 42 from Users where Email = @email and Id <> @Id ) then Cast( 1 as Bit )
    else Cast( 0 as Bit ) end as EmailBelongsToAnotherUser

This is somewhat more efficient as there is no need to create a recordset to pass a single bit.

Note that this should really be done in the same transaction that updates the database. Otherwise someone else might sneak in and you'll have a conflict.

Upvotes: 2

Damon
Damon

Reputation: 3010

Try this:

create procedure CheckDuplicate
@email nvarchar(256),
@Id int
As
Begin

set nocount on;

   Select Case When Exists (
      select usr.Id, usr.UserName, usr.Email
      from Users usr
      where usr.Email = @email and usr.Id = @Id
   )
   Then Cast(1 as bit)
   Else Cast(0 as bit) 
   End


End

Upvotes: 2

Hiten004
Hiten004

Reputation: 2481

--IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')

create procedure CheckDuplicate
@email nvarchar(256),
@Id int
As
Begin
set nocount on;
IF EXISTS (select usr.Id, usr.UserName, usr.Email
from Users usr
where usr.Email = @email and usr.Id = @Id)
begin
Print 'YES' 
end
else
begin
Print 'NO' 
end

end

Upvotes: 0

Related Questions