-1

When I execute the code below I am getting unknown column 'total' in 'field list' error.

DELIMITER$$
CREATE PROCEDURE setAuthentication(IN uname varchar(12), IN oldpws VARCHAR(12), IN newpws VARCHAR(12), OUT status INT)
BEGIN
    call getAuthentication(uname,oldpws,@total,@pwsval);
    IF(total=1 AND pwsval IS NOT NULL) THEN
    	UPDATE authentication SET password = newpws WHERE loginId=uname and password=oldpws;
        SET status = 1;
    ELSE
        SET status = 0;
    END IF;
END$$
DELIMITER;

What is the root cause of the problem?. Your help is kindly appreciated.

Edited by solomon_13000: n/a

2
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by solomon_13000
0

I received the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@total INT ;
DECLARE @pwsval VARCHAR(12) ;
call getAuthentication(unam' at line 3

DELIMITER$$
CREATE PROCEDURE setAuthentication(IN uname varchar(12), IN oldpws VARCHAR(12), IN newpws VARCHAR(12), OUT status INT)
BEGIN
    DECLARE @total INT;
    DECLARE @pwsval VARCHAR(12);
    call getAuthentication(uname,oldpws,@total,@pwsval);
    IF(@total=1 AND @pwsval IS NOT NULL) THEN
    	UPDATE authentication SET password = newpws WHERE loginId=uname and password=oldpws;
      SET status = 1;
    ELSE
      SET status = 0;
    END IF;
END$$
DELIMITER;
0

Now it works.

DELIMITER$$
CREATE PROCEDURE setAuthentication(IN uname varchar(12), IN oldpws VARCHAR(12), IN newpws VARCHAR(12), OUT status INT)
BEGIN
    DECLARE var1 INT;
    DECLARE var2 VARCHAR(12);
    call getAuthentication(uname,oldpws,@total,@pwsval);
    SET @var1 = @total;
    SET @var2 = @pwsval;
    SELECT @var1;
    SELECT @var2;
    IF(@var1=1 AND @var2 IS NULL) THEN
    	UPDATE authentication SET password = newpws WHERE loginId=uname and password=oldpws;
        SET status = 1;
    ELSE
        SET status = 0;
    END IF;
END$$
DELIMITER;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.