儲存過程準備好的語句沒有錯誤並且不起作用

這是我的儲存過程：

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

它在很大程度上是一項正在進行中且尚未完成的工作。biPK（參數 1）是可選的，如果為 NULL，則它不會插入到查詢中。

我添加了SELECT txtSQL以便查看創建的最終 SQL 是否有效。沒有報錯，生成的SQL有效，但是表中沒有插入記錄。

我已經複製了生成的插入 SQL 並粘貼到HeidiSQL 中並且它可以工作，但是呼叫儲存過程並不會最終創建任何新記錄。

這是在 Windows 版本10.0.19041.928上的 MariaDB 版本10.5.9.0上執行的

我錯過了什麼？

$$ Edit $$修改，但仍然不起作用：

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

Doh，我在網上查看了許多範例，這些範例與我在範例中使用的幾乎相同，但是我確實認為我遺漏了一些東西，然後我找到了另一個證明我遺漏的範例：

EXECUTE stmt;

所以完整的準備好的語句如下所示：

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

首先，永遠不要使用與列名相同的變數名，數據庫可能會混淆。

其次可以使用雙引號和單引號，實現字元串

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 |
| :------------------------------------------------------ |
| 插入元標記（`biPK`，`vcTag`）值（'a'，'b'）；|

✓

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/290978