CODEWITHSUNDEEP
mysqlstored-procedures
Nitish Hardeniya
Nitish Hardeniya

Reputation: 181

How to recieve a query from parameter in mysql stored procedure

I have a stored procedure where I want to perform a INSERT operation. My INSERT query is stored in a php variable $query and I want to pass it as a parameter to my stored procedure.

$query_procedure = "CALL AddStation('$query','@LID')"

How can I get this query when I am creating the stored procedure?

Upvotes: 0

Views: 932

Answers (1)

peterm
peterm

Reputation: 92835

Actually I want to use the same stored procedure for different INSERT queries so that I dont have to pass individual parameters to the stored procedure.

Although you can technically achieve this, as shown below, I strongly discourage you from doing it. This a very bad idea. It simple doesn't make any sense and adds no value to your code. It's vulnarable to sql injections. You loose ability to use prepared statements for insert statements themselves. It's fragile and prone to errors since you're passing query strings, etc...

You better off without a stored procedure like this at all. Just use prepared statements in your client code.

DELIMITER$$
CREATE PROCEDURE AddARow(IN _sql TEXT, OUT _lid INT)
BEGIN
  SET @sql = _sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  SET _lid = LAST_INSERT_ID();
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Sample usage:

SET @lid = NULL;
SET @sql = 'INSERT INTO tablest (STATION_NAME, GEOGRAPHY) VALUES (''station1'',''India'')';
CALL AddARow(@sql, @lid);
SELECT @lid;

Here is SQLFiddle demo

Upvotes: 2

Related Questions