Solved

call another MySQL SP from within a MySQL SP

Posted on 2013-11-22
8
370 Views
Last Modified: 2013-11-26
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
Comment
Question by:marrowyung
  • 6
  • 2
8 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
For example please. It seems I don't have too.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
it seems that we can't do IF NOT EXISTs then create trigger logic ?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"& 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"& 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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
IF you need to

like any returned value, IF you need to make a decision later based on that value
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I don't understand this:

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

please give some example.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Creating and Managing Databases with phpMyAdmin in cPanel.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now