How create a Stored procedure that will accept a parameter

Question

I have a "set" of SQL statements

DROP TABLE IF EXISTS data.s;
CREATE TABLE data.s LIKE data._style;
INSERT INTO data.s Values (?,?,?,?,?,?,?,?,?,?,?,?,?);

UPDATE data.s n JOIN data.s_ o ON n.ID = o.ID SET n.TC = o.TC;
UPDATE data.s n JOIN data.s_ o ON n.ID = o.ID SET n.VR = o.VR;
UPDATE data.s n JOIN data.o_ o ON n.ID = o.ID SET n.OC = o.OC;
DELETE FROM data.s WHERE TC <= 0;
DELETE FROM data.s WHERE TC < 100;
DELETE FROM data.s WHERE OC < 100 ;

Using "s" table as example, How would I create a SP where "s" is a variable, which could be replace with t, u v, z...... whatever? I would like to change this variable with a SQL call statement.

score 2 · Accepted Answer · edited May 23 '17 at 12:11

2

MySQL does not handle real dynamic SQL, so you have to use prepared statement.

Look at the accepted answer : How To have Dynamic SQL in MySQL Stored Procedure and especially the link he gives (Dynamic SQL part).

Something like :

CREATE PROCEDURE `execute`(IN sqlQuery varchar(255))
BEGIN

    set @sqlQuery := sqlQuery;

    prepare stmp from @sqlQuery; 

    execute stmp;

    deallocate prepare stmp; 

END

CREATE PROCEDURE `yourProcName`(IN tableName varchar(50))
BEGIN

    call execute(concat('DROP TABLE IF EXISTS ', tableName));
    call execute(concat('CREATE TABLE ', tableName, ' LIKE data._style'));

    ...

END

edited May 23 '17 at 12:11

Community

1
1

answered Mar 02 '11 at 20:34

Xavinou

802
1
6
18

So 1st SP is execute engine for 2nd SP, correct? I understand this. :-). There are no changes to execute SP. Ok, How would I call the 2nd Sp with tablename? 'Call yourProcName, := tablename', This is close!!! – Merlin Mar 02 '11 at 22:59
To call the second SP, just run a query `call yourProcName('tablename')` – Xavinou Mar 02 '11 at 23:02

How create a Stored procedure that will accept a parameter

1 Answers1