CODEWITHSUNDEEP
mysqlsqlstored-procedures
Merlin
Merlin

Reputation: 25699

How create a Stored procedure that will accept a parameter

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.

Upvotes: 0

Views: 403

Answers (1)

Xavinou
Xavinou

Reputation: 802

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

Upvotes: 2

Related Questions