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.

DECLARE @total INT

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;

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;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.