CODEWITHSUNDEEP
mysqlstored-procedures
srv8
srv8

Reputation: 53

MySql variable stored with select count statement always returns 1

Hello in a test database i have only one row in the table customer.I have created a stored procedure to do some work. Every time i execute this query

  select count(*) from Customer where Email= ....

in my database i get a count 0 but in my stored procedure the result is always 1 without any error.This is the code inside my stored procedure.

  BEGIN
  START TRANSACTION;
  SET @var=(select count(*) from Customer where Email=email);
  select @var;
  if (@var>0) then
    update Customer Set Password=temppass,TemporaryPassword=temppass where 
    Email=email;
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
  END

Any ideas please?Thanks!

Upvotes: 1

Views: 390

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The problem is your condition Email = email. This is always going to be true -- because both references to email are referring to the same thing, the email column in the table.

Call the variable by a different name. I often use the suffix v_, so where customer.email = v_email.

That said, I think you can just do:

begin
    update Customer c
        set c.Password = v_temppass,
            c.TemporaryPassword = v_temppass
    where c.email = v_email;
end;

I don't see what value the transaction gives.

I assume that you are not storing passwords as clear-text. That is definitely a no-no if you care at all about security and access.

Upvotes: 1

Related Questions