MySQL transaction procedure: Unknown column in 'where clause'

Question

I'm trying to update a table and return the updated value as a part of a transaction:

CREATE PROCEDURE updateCount(IN dataname VARCHAR(255) )
    LANGUAGE SQL
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    START TRANSACTION;
        update CountTable set count = count + 1 where productName = dataname;
        set data = @data = (SELECT count FROM CountTable WHERE productName = dataname);
        select @data;
    commit;
 
CREATE TABLE CountTable (
    id varchar(255) primary key,

    productName varchar(255),
    count long
);

But on calling the function, I get this error:

Error Code: 1054. Unknown column 'productName' in 'where clause'

I've given the parameters the way I've found in much documentation. Also, if I hard-code the parameter value in the function, I get this:

Error Code: 1193. Unknown system variable 'data'

Answer 1

The following SP works fine:

DELIMITER $$

DROP PROCEDURE IF EXISTS `updateCount`$$

CREATE PROCEDURE `updateCount`(IN dataname VARCHAR(255) )
BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    START TRANSACTION;
        UPDATE CountTable SET COUNT = COUNT + 1 WHERE productName = dataname;
        SET @data = (SELECT COUNT FROM CountTable WHERE productName = dataname);
        SELECT @data;
    COMMIT;
END$$

DELIMITER ;

Nothing much, just SET data = @data = changed to SET @data =.

But standardswise, there are other issues too. The table should not have count long. Trying that as-is makes that column of type mediumtext. There is no long type in MySQL. There is BIGINT, you should use that.

Also, id would usually be some numeric type column with auto-increment. varchar(255) also works, but just that it is not ideal.

CREATE TABLE `counttable` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `productName` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `count` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Also, if you are updating table by productName, probably it would be marked as a unique key too, to make sure there aren't multiple products with the same name.

After these changes,

CALL updateCount('abc');

@data
--------
51

It returns an incremented value.