Stored procedure prepared statement no error and not working

All we need is an easy explanation of the problem, so here it is.

Here is my stored procedure:

BEGIN
#Procedure:     insertMetaTagIfNotPresent
#Parameters:    bkID    ID to find or NULL to create automatically
#               vcTag   Tag name to assign, must be unique
#Notes:         Creates metatag table if it doesn't exist
#History:       2021/05/04  Written by Simon Platten

    DECLARE txtSQL TEXT;
    #Create SQL insert string
    SET txtSQL := 'INSERT INTO metatags (';
    #Has a primary key value been supplied?
    IF NOT biPK IS NULL THEN
    #Yes, use it!
      SET txtSQL := CONCAT(txtSQL, '`biPK`,');
    END IF;
    SET txtSQL := CONCAT(txtSQL, '`vcTag`) VALUES (');
    IF NOT biPK IS NULL THEN
        SET txtSQL := CONCAT(txtSQL, biPK, ',');
    END IF;
    SET txtSQL := CONCAT(txtSQL, '\'', vcTag, '\');');
    #Create prepared statment
    SET @SQL := txtSQL;
    PREPARE stmt FROM @SQL;
    DEALLOCATE PREPARE stmt;
    SELECT txtSQL;
END

Its very much a work in progress and not finished. biPK (parameter 1) is optional and if NULL then it isn’t inserted into the query.

I’ve added SELECT txtSQL so I can see if the final SQL created is valid. No error occurs and the SQL generated is valid, but no record is inserted into the table.

I have copied the generated insert SQL and pasted into HeidiSQL and it works, however calling the stored procedure doesn’t end up with any new record being created.

This is running on MariaDB version 10.5.9.0 on Windows version 10.0.19041.928

What am I missing?

[Edit] modifications, but still doesn’t work:

BEGIN
#Procedure:       insertMetaTagIfNotPresent
#Parameters:      _bkID ID to find or NULL to create automatically
#                 _vcTag      Tag name to assign, must be unique
#Notes:           Creates metatag table if it doesn't exist
#History:         2021/05/04  Written by Simon Platten
 
      DECLARE txtSQL TEXT;
      #Create SQL insert string
      SET txtSQL := "INSERT INTO metatags (";
      #Has a primary key value been supplied?
      IF NOT _biPK IS NULL THEN
      #Yes, use it!
        SET txtSQL := CONCAT(txtSQL, "`biPK`,");
      END IF;
      SET txtSQL := CONCAT(txtSQL, "`vcTag`) VALUES (");
      IF NOT _biPK IS NULL THEN
            SET txtSQL := CONCAT(txtSQL, "'", _biPK, "',");
      END IF;
      SET txtSQL := CONCAT(txtSQL, "'", _vcTag, "');");
      #Create prepared statment
      SET @SQL := txtSQL;
      PREPARE stmt FROM @SQL;
      DEALLOCATE PREPARE stmt;
      SELECT txtSQL;
END

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Doh, I looked at numerous examples online, and these were pretty much the same as I used in my example, however I did think I was missing something, then I found another example which proved that I was missing:

EXECUTE stmt;

So the complete prepared statement looks like:

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Method 2

First of all never use variable names that are the same as column names, the databases can get confused.

Second you can use doppel quotes and single quotes, to achieve strings

CREATe PROCEDURE abc(IN _biPK varchar(10),_vcTag varchar(10))
BEGIN
#Procedure:     insertMetaTagIfNotPresent
#Parameters:    bkID    ID to find or NULL to create automatically
#                   vcTag   Tag name to assign, must be unique
#Notes:         Creates metatag table if it doesn't exist
#History:       2021/05/04  Written by Simon Platten

    DECLARE txtSQL TEXT;
    #Create SQL insert string
    SET txtSQL := 'INSERT INTO metatags (';
    #Has a primary key value been supplied?
    IF NOT _biPK IS NULL THEN
    #Yes, use it!
      SET txtSQL := CONCAT(txtSQL, '`biPK`,');
    END IF;
    SET txtSQL := CONCAT(txtSQL, '`vcTag`) VALUES (');
    IF NOT _biPK IS NULL THEN
        SET txtSQL := CONCAT(txtSQL,"'", _biPK, "',");
    END IF;
    SET txtSQL := CONCAT(txtSQL, "'", _vcTag, "');");
    SELECT txtSQL;
    #Create prepared statment
    #SET @SQL := txtSQL;
    #PREPARE stmt FROM @SQL;
    #DEALLOCATE PREPARE stmt;
    #SELECT txtSQL;
END
CALL abc('a','b')
| txtSQL                                                  |
| :------------------------------------------------------ |
| INSERT INTO metatags (`biPK`,`vcTag`) VALUES ('a','b'); |

✓

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply