CODEWITHSUNDEEP
mysqlasp.net
PCG
PCG

Reputation: 2299

How do I join multiple SELECTs in stored procedure?

I need to send LOGIN_LOGIN_ATTEMPTS & LOGIN_ACCOUNT_STATUS always regardless of password (PassWD) match. The following script works correctly only if the password is matched. When it's not matched, it send all null (but I attempts and status). What's the best way to send LOGIN_LOGIN_ATTEMPTS & LOGIN_ACCOUNT_STATUS when an email address is correct but password and or Status is not matched?

I could add another SELECT statement but the final result should be one line with rest of the fields null.

The following is from my stored procedure.

SELECT
login.LOGIN_USER_ID,
login.LOGIN_EMAIL,
login.LOGIN_LOGIN_ATTEMPTS,
login.LOGIN_ACCOUNT_STATUS,
GROUP_CONCAT(user_role.USER_ROLE_ROLE SEPARATOR ',') AS ROLES,
user_role.USER_ROLE_STATUS 
FROM login 
INNER JOIN user_role ON 
user_role.USER_ROLE_USER_ID = login.LOGIN_USER_ID AND user_role.USER_ROLE_STATUS = @AccStatus 
WHERE login.LOGIN_EMAIL = @UserEmail AND login.LOGIN_ACCOUNT_STATUS = @AccStatus AND login.LOGIN_PASSWORD = @PassWD;

SET @USER_FOUND = found_rows();

UPDATE login SET 
LOGIN_ACCOUNT_STATUS = (SELECT CASE (LOGIN_LOGIN_ATTEMPTS>@LoginAttempts) WHEN 1 THEN 'LOCKED' ELSE 'ACTIVE' END),
LOGIN_LOGIN_ATTEMPTS = (SELECT CASE (@USER_FOUND) WHEN 0 THEN LOGIN_LOGIN_ATTEMPTS + 1 ELSE 0 END),
LOGIN_LAST_LOGIN_DATE = (SELECT CASE (@USER_FOUND) WHEN 1 THEN @TransactionDateTime ELSE LOGIN_LAST_LOGIN_DATE END),
LOGIN_LAST_LOGIN_LOCATION = null 
WHERE LOGIN_EMAIL=@UserEmail;

Upvotes: 0

Views: 65

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

Your code works fine from what I can guess of the missing bits in the question - and here's the proof.

drop procedure if exists p;
delimiter $$

CREATE  PROCEDURE `p`(
    IN `inacount_status` int,
    IN `inuseremail` varchar(3),
    IN `inpasswd` varchar(3),
    IN `inLoginAttempts` int
)
begin

set @acount_status = inacount_status;
set @useremail = inuseremail;
set @passwd = inpasswd;
Set @LoginAttempts = inloginattempts;

SELECT
login.LOGIN_USER_ID,
login.LOGIN_EMAIL,
login.LOGIN_LOGIN_ATTEMPTS,
login.LOGIN_ACCOUNT_STATUS
FROM login 
WHERE login.LOGIN_EMAIL = @UserEmail AND login.LOGIN_ACCOUNT_STATUS = @AccStatus AND login.LOGIN_PASSWORD = @PassWD;

SET @USER_FOUND = found_rows();

select found_rows();

UPDATE login SET 
LOGIN_ACCOUNT_STATUS = (SELECT CASE (LOGIN_LOGIN_ATTEMPTS>@LoginAttempts) WHEN 1 THEN 'LOCKED' ELSE 'ACTIVE' END),
LOGIN_LOGIN_ATTEMPTS = (SELECT CASE (@USER_FOUND) WHEN 0 THEN LOGIN_LOGIN_ATTEMPTS + 1 ELSE 0 END),
#LOGIN_LAST_LOGIN_DATE = (SELECT CASE (@USER_FOUND) WHEN 1 THEN @TransactionDateTime ELSE LOGIN_LAST_LOGIN_DATE END),
LOGIN_LAST_LOGIN_LOCATION = null 
WHERE LOGIN_EMAIL=@UserEmail;

end $$

delimiter ;

drop table if exists login;
create table login
(LOGIN_USER_ID int,
lOGIN_EMAIL varchar(3),
LOGIN_LOGIN_ATTEMPTS int,
LOGIN_ACCOUNT_STATUS varchar(10),
login_password varchar(3),
login_last_login_location varchar(10));

insert into login values
(1,'aaa',0,0,'xxx','loc');
insert into login values
(2,'bbb',0,0,'yyy','loc');

set @acount_status = 0;
set @useremail = 'aaa';
set @passwd = 'bbb';
Set @LoginAttempts = 2;

call p(@account_status,@useremail,@passwd,@loginattempts);
select * from login;

  +---------------+-------------+----------------------+----------------------+----------------+---------------------------+
| LOGIN_USER_ID | lOGIN_EMAIL | LOGIN_LOGIN_ATTEMPTS | LOGIN_ACCOUNT_STATUS | login_password | login_last_login_location |
+---------------+-------------+----------------------+----------------------+----------------+---------------------------+
|             1 | aaa         |                    1 | ACTIVE               | xxx            | NULL                      |
|             2 | bbb         |                    0 | 0                    | yyy            | loc                       |
+---------------+-------------+----------------------+----------------------+----------------+---------------------------+
2 rows in set (0.02 sec)

If you are doing something different in any way please enrich your question and comment appropriately.

Upvotes: 1

Related Questions