Stored procedure Returning unexpected value while implementing AND Oprator

[morfious90]

I'm implement if else in stored procedure with and oprator .

If both Conditions are true the if statement should return 1 else -1.

here username and email should not be duplicate please review query below.

alter proc spUserdetailss
   @name Nvarchar(50),
   @passwords Nvarchar(100),
   @emailadd Nvarchar(50),
   @Gender nvarchar(50)
as 
begin
   declare @Ucount int
   declare @Ecount int
   declare @returncode int

   select @Ucount = count(Username), @Ecount= Count(Email)
   from userdata 
   where Username=@nameUsername = @name and Email=@emailadd
Email = @emailadd

   if @Ucount > 0 and @Ecount >0
   Begin
      set @returncode =-1
   end
   else
   begin
      set @returncode = 1


      insert into userdata 
      values(@name, @passwords, @emailadd, @Gender)
   end


   select @returncode as ReturnValue 


end

[hericles]

You say in your post "If both Conditions are true the if statement should return 1 else -1."
But your code does the exact opposite:

if @Ucount > 0 and @Ecount >0
   Begin
      set @returncode =-1
   end
else
   begin
      set @returncode = 1

Which is incorrect, your if statement or your post?

[AmrMohammed]

Greetings

I want to to tell you something about your Query performance try as much as possible not to use function COUNT() and use EXISTS instead

Replace your Query with

 IF EXISTS (SELECT * 
            FROM TABLE_NAME 
            WHERE COLUMN_NAME = Value_Specified
            AND   COLUMN_NAME = Value_Specified)
 SELECT 1 AS Found_Result
 ELSE
 SELECT -1 AS Not_Found_Result

OR

IF  (SELECT * 
     FROM TABLE_NAME 
     WHERE COLUMN_NAME = Value_Specified
     AND   COLUMN_NAME = Value_Specified)           
SELECT 1 AS Found_Result
ELSE
SELECT -1 AS Not_Found_Result

[adam_k]

I believe that you problem doesn't lie with the if, but with how you give values to @Ucount and @Ecount:

select @Ucount = count(Username), @Ecount= Count(Email)
from userdata 
where Username=@nameUsername = @name and Email=@emailadd

If I give Username = aa and Email = '[e-mail]' and neither exists then it's OK, you insert the record. But if let's say the username exists in the table, but with a different e-mail - or the other way around - then you won't be able to see that as you are testing for both the name and the e-mail.
You need to either split the value assignment into 2 different selects:

select @Ucount = count(*) 
from userdata 
where Username = @nameUsername 

select @Ecount = count(*) 
from userdata 
where Email = @emailadd 

or drop entirely the second variable (It doesn't look that you are using it anyway) and go with:

select @Ucount = count(*)
from userdata 
where Username = @nameUsername or Email = @emailadd

This way you get the number of records with a match in either field. So in the above example if a user with username = 'aa' and a different user with e-mail = '[e-mail]' existed in your table, you would get a @Ucount = 2 instead of 0 that your query would return.

[isozworld]

If your want to return a value from your sp the better way is output parameters.
just try to check this link
Click Here

[pclfw]

Ever thought about using the DB itself?

Set the username and emailaddress as a concatonated unique index and try the insert. If you have a duplicate the insert will fail. This is a much faster way of ensuring uniqueness.