MySQL dynamic stored procedure not returning correct results | possible single quotes Incorrect

Question

I am writing a dynamic MySQL 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.

I suspect the issue could be trying to add single quotes for the v_Value, because I added an instance into my DB in v_Tablename Employee in v_ColumnName Email with v_Value 'testing123@gmail.com'. I should expect 1 result to come from running this query.

When I run a SELECT query in plain SQL like this:

SELECT COUNT(*) 
FROM Employee 
WHERE Email = 'testing123@gmail.com';

This returns 1 result (correct, expected).

But when I call my stored procedure:

CALL CheckValueExists('Employee', 'Email', 'testing123@gmail.com', @result);
SELECT @result;

This returns 0 (incorrect, not expected).

I suspect the single quotes for v_Value might be causing this. 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
  DECLARE v_Count INT;

  SET @query = CONCAT('SELECT COUNT(*) FROM ', v_Tablename,
                      ' WHERE ', v_ColumnName, ' = ?');

  SET @v_Value = CONCAT('', v_Value, '');

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

  -- Get the count from the variable @count
  SELECT @count INTO v_Count;

  -- Set v_Exists based on the count
  IF v_Count > 0 THEN
    SET v_Exists = TRUE;
  ELSE
    SET v_Exists = FALSE;
  END IF;
END;

But even if I assign v_Value like SET @v_Value = v_Value; I still get 0 results when running my stored procedure. Does anyone know if the single quotes could be causing this, or if not what might be the reason the stored procedure is not behaving as expected?

Thanks in advance.

Answer 1

Per @Akina in the comments: You assign the value to @count nowhere. Try .. CONCAT('SELECT COUNT(*) INTO @count FROM ', ..