?
Solved

call another MySQL SP from within a MySQL SP

Posted on 2013-11-22
8
Medium Priority
?
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39670887
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
ID: 39672461
For example please. It seems I don't have too.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39673656
it seems that we can't do IF NOT EXISTs then create trigger logic ?
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 1

Author Comment

by:marrowyung
ID: 39673668
"& 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
 
LVL 1

Author Comment

by:marrowyung
ID: 39676665
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39676984
"& 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 49

Expert Comment

by:PortletPaul
ID: 39677221
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
ID: 39679709
I don't understand this:

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

please give some example.
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 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