Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

call another MySQL SP from within a MySQL SP

Dear all,

can I simply called a SP within another SP ? like

CREATE PROCEDURE `create_auditriggers` (IN databaseName VARCHAR(40))  	
BEGIN  
DECLARE a, b, finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE current_tablelist VARCHAR(50) DEFAULT ""; 
DECLARE fullexcutecmd VARCHAR(5000);
DECLARE fullexcutecmd2 VARCHAR(5000) ;

/*Declare and populate the cursor with a SELECT statement */  	
	
DECLARE tablename CURSOR FOR 	
SELECT DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.COLUMNS	
    WHERE TABLE_SCHEMA=databaseName;   	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;     
/*Specify what to do when no more records found, notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures*/  	
OPEN tablename;	

	get_tabeslist: LOOP	
 
   FETCH tablename INTO current_tablelist;	
         IF finished = 1 THEN 	
          LEAVE  get_tabeslist;	
          END IF;	
    
 call Create_BEFORE_DELETE_TRIGGER ('databaseName','QRTZ_BLOB_TRIGGERS')

  
  END LOOP  get_tabeslist;	
close tablename;                             

END $$
DELIMITER ;

Open in new window


or I need to do Concat to build the whole string of calling SP again:

SET @fullexcutecmd =  CONCAT(' call Create_BEFORE_DELETE_TRIGGER ('databaseName',dhfahfdfdsfl')
,';');  

PREPARE stmt FROM @fullexcutecmd;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

Open in new window

0
marrowyung
Asked:
marrowyung
  • 6
  • 2
1 Solution
 
PortletPaulCommented:
yes, you can call a procedure inside another procedure like this:

call Create_BEFORE_DELETE_TRIGGER ('databaseName','QRTZ_BLOB_TRIGGERS')

& if you define the inner procedure with "out" parameters, then the outer procedure can react to the result (if that is needed).
see: http://dev.mysql.com/doc/refman/5.0/en/call.html
0
 
marrowyungAuthor Commented:
For example please. It seems I don't have too.
0
 
marrowyungAuthor Commented:
it seems that we can't do IF NOT EXISTs then create trigger logic ?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
marrowyungAuthor Commented:
"& if you define the inner procedure with "out" parameters, then the outer procedure can react to the result (if that is needed).
see: http://dev.mysql.com/doc/refman/5.0/en/call.html "

is that mean somehting like, when it return something out,we can use set @variable = for that ?
0
 
marrowyungAuthor Commented:
0
 
marrowyungAuthor Commented:
"& if you define the inner procedure with "out" parameters, then the outer procedure can react to the result (if that is needed)."

how can we make use of it ?
0
 
PortletPaulCommented:
IF you need to

like any returned value, IF you need to make a decision later based on that value
0
 
marrowyungAuthor Commented:
I don't understand this:

"IF you need to make a decision later based on that value"

please give some example.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now