I have some MySQL procedures to do the following:
-
The main code will always call the procedure
CALL sp_syncTabela
. -
The
sp_syncTabela
will check if there is another specific procedure fortableName
passed as argument. If it exists, it will be called; if not, the default procedure "sp_syncExecuteQuery
" will be called instead. -
For example, I have
sp_syncTabela_caixa
in the database. So when I runCALL sp_syncTabela (..."caixa"...)
, it goes internally to callCALL sp_syncTabela_caixa
. But a call toCALL sp_syncTabela (..."outra"...)
will only use the default proceduresp_syncExecuteQuery
. -
All specific procedures have the format
sp_syncTabela_???
, as the "box" has. These procedures are for performing other custom tasks in addition to callingCALL sp_syncExecuteQuery
. -
The logic is: Does the table referenced by the
tableName
argument have a specific procedure? If so, do some custom tasks on the table and then callsp_syncExecuteQuery
; but if it does not, just runsp_syncExecuteQuery
with no extra tasks.
I do not see any recursive calls, but I'm getting the error
Error Code: 1444. The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner.
This is happening after running CALL sp_syncTabela ("I", 33, "caixa", "id='20',fundo='11.11',abertura_user_id='2',abertura_data='2014-11-11 18:24:33',fechamento_user_id=NULL,fechamento_data=NULL,", "id=20")
.
Now, the codes:
CREATE DEFINER='root'@'localhost' PROCEDURE 'sp_syncTabela'(IN action VARCHAR(10), IN tableName VARCHAR(50), IN columnData VARCHAR(4000))
BEGIN
DECLARE res Int;
SELECT Count(*) INTO res FROM information_schema.Routines WHERE routine_name = Concat("sp_syncTabela_", tableName);
CASE res
WHEN 0 THEN CALL sp_syncExecuteQuery (action, tableName, columnData);
ELSE
BEGIN
SET @querySync = Concat("CALL sp_syncTabela_", tableName, " (\"", action, "\", \"", tableName, "\", \"", columnData, "\");");
PREPARE tablePreviewQuery FROM @querySync;
EXECUTE tablePreviewQuery;
END;
END CASE;
END
CREATE DEFINER='root'@'localhost' PROCEDURE 'sp_syncTabela_caixa'(IN action VARCHAR(10), IN tableName VARCHAR(50), IN columnData VARCHAR(4000))
BEGIN
/* Apenas uma procedure específica de exemplo, com uma tarefa adicional antes de chamar sp_syncExecuteQuery */
INSERT INTO configuration (name, value) VALUES ("Última chamada à sp_syncTabela_caixa", Cast(Now() as Char))
ON DUPLICATE KEY UPDATE value = Cast(Now() as Char);
CALL sp_syncExecuteQuery (action, tableName, columnData);
END
CREATE DEFINER='root'@'localhost' PROCEDURE 'sp_syncExecuteQuery'(IN action VARCHAR(10), IN tableName VARCHAR(50), IN columnData VARCHAR(4000))
BEGIN
CASE action
WHEN "I" then CALL sp_generateInsertQuery (tableName, columnData, @queryToExecute);
WHEN "U" then CALL sp_generateUpdateQuery (tableName, columnData, @queryToExecute);
WHEN "D" then CALL sp_generateDeleteQuery (tableName, columnData, @queryToExecute);
ELSE Begin End;
END CASE;
PREPARE tablePreviewQuery FROM @queryToExecute;
EXECUTE tablePreviewQuery;
END
I have already looked for help on various websites, in Portuguese and English, for keywords like 'nested procedures', 'recursive errors', and the like. And, furthermore, I do not see where there is recursion in this process. When I directly execute sp_syncTabela_caixa
, no error occurs.