Search code examples
sqlmysqlstored-procedures

MySQL stored procedure throwing [42000][1064] you have an error in your SQL syntax on EXECUTE stat USING statement

I am writing a stored procedure that does the following:

In a given table v_Tablename, if the count of the number of rows in v_ColumnName that equal to v_Value is greater than 0, return true. Otherwise, return false. Some examples where this stored procedure can be used:

  • Return if exists a person in Person table (v_Tablename) where Age (v_ColumnName) is 23 (v_Value)
  • Return if exists an email in Employee table (v_Tablename) where Email (v_ColumnName) is testing123@gmail.com (v_Value).

This is my code:

create
    definer = root@localhost procedure CheckValueExists(
    IN v_Tablename VARCHAR(100),
    IN v_ColumnName VARCHAR(100),
    IN v_Value VARCHAR(100),
    OUT v_Exists BOOLEAN
)
BEGIN
  SET @query = CONCAT('SELECT COUNT(*) FROM ', v_Tablename,
                      ' WHERE ', v_ColumnName, ' = ?');

  PREPARE stat FROM @query;
  EXECUTE stat USING v_Value;
  DEALLOCATE PREPARE stat;

  GET DIAGNOSTICS v_Exists = ROW_COUNT > 0;
END;

No matter how many ways I format this, I keep getting these errors:

[42000][1064] 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 'v_Value;
DEALLOCATE PREPARE stat;
GET DIAGNOSTICS v_Exists = ROW_COUNT > 0;' at line 13

I think it doesn't like EXECUTE stat USING v_Value, but I need this line because it assigns ? to v_Value to do the check.

ROW_COUNT > 0 also doesn't like > but this is how I check if the results is > 0, which tells me if there exists values.

Does anyone know how I can correct my stored procedure?

Solution

  • This is the problem:

    EXECUTE stat USING v_Value;

    https://dev.mysql.com/doc/refman/8.0/en/execute.html says:

    Parameter values can be supplied only by user variables...

    This refers to MySQL user-defined variables, the kind with the @ sigil.

    Local variables like the IN parameters and local variables declared in the stored routine with DECLARE are not supported as arguments to EXECUTE.

    It might seem needless, but a way to get this to work is:

    SET @v_Value = v_Value;
EXECUTE stat USING @v_Value;

    The SET statement creates a user-defined variable with a similar name as your input parameter (the name of the variable doesn't matter, I just chose a similar name in this example).