MySQL Forums
Forum List  »  Stored Procedures

Re: cannot call a stored procedure from within another stored procedure
Posted by: Peter Brawley
Date: October 23, 2021 01:04PM

drop table if exists logging;
create table logging( ts timestamp default now(), log_string text );
drop procedure if exists Write_to_log;
drop function if exists Prepare_values_string;
drop procedure if exists test;
delimiter go

CREATE PROCEDURE `write_to_log`(_STRING VARCHAR(5000))
BEGIN
  START TRANSACTION;  -- NEEDS ERROR TRAPPING
  INSERT INTO logging (LOG_STRING) VALUES (_STRING);
  COMMIT;
END;
go

CREATE FUNCTION Prepare_values_string(valueString TEXT) 
      RETURNS varchar(5000) CHARSET latin1
BEGIN
  DECLARE _val TEXT;
  DECLARE _pos INT;
  DECLARE _TAB CHAR;
  DECLARE _COMMA CHAR;
  DECLARE _len INT;
  DECLARE _return_ TEXT;
  SET _TAB = x'0009';
  SET _COMMA := x'0044';
  IF ENDS_WITH(valueString) <> _TAB THEN
    SET valueString = CONCAT(valueString, _TAB);  -- NOT :=
  END IF;
  SET _return_ := '';
  SET _len = 	LENGTH(valueString);
  aWhileLoop : WHILE _len <> 0 DO
   	SET _pos = locate(_TAB, valueString);
    SET _val = substring(valueString, 1, _pos -1);
    SET _val = quote(_val);
    SET _return_ := concat(_return_, _COMMA, _val);
    SET valueString := substring(valueString, _pos +1);
    CALL WRITE_TO_LOG(valueString);
    SET _len :=	LENGTH(valueString);
  END WHILE aWhileLoop;
  RETURN concat('(', _return_, ')');
END;
go

create procedure test() 
begin
  select * from logging;
  call Write_to_log("this is a test");
  select "After call to write_to_log", ts, log_string from logging;
end;
go
delimiter ;
call test();
+----------------------------+---------------------+----------------+
| After call to write_to_log | ts                  | log_string     |
+----------------------------+---------------------+----------------+
| After call to write_to_log | 2021-10-23 13:58:16 | this is a test |
+----------------------------+---------------------+----------------+

If you're on Linux, be careful about capitalising & proper-casing database object names.



Edited 1 time(s). Last edit at 10/23/2021 01:08PM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: cannot call a stored procedure from within another stored procedure
144
October 23, 2021 01:04PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.