Passing columns as parameters in stored procedure MySQL-CodePudding

I'm trying to make a stored procedure, using phpMyAdmin, that has 2 columns as parameters. This is the SQL code that I have to transform into a stored procedure with parameters:

SELECT c1.tara, c2.tara
FROM clase c1 JOIN clase c2 ON (c1.clasa > c2.clasa)
WHERE c1.deplasament = c2.deplasament AND c1.tip = c2.tip;

This is the definition of the table:

CREATE TABLE Clase (
    clasa VARCHAR2(30),
    tip VARCHAR2(12),
    tara VARCHAR2(30),
    nr_arme NUMBER(4,0),
    diametru_tun VARCHAR2(20),
    deplasament VARCHAR2(20));

The query displays the country pairs which have the same deplasament and tip.

To create the stored procedures I'm using the "create new routine" option from phpMyAdmin. I know that for this kind of problem I have to use prepared statements. I did the following:

SET @s=CONCAT('SELECT c1.tara, c2.tara FROM clase c1 JOIN clase c2 ON (c1.clasa > c2.clasa) WHERE c1.',p_col1,'=c2.',p_col1,' AND c1.',p_col2,'=c2.',p_col2);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

p_col1 and p_col2 are IN VARCHAR parameters. When I click 'execute' I get the following error code: #1064, which is a syntax error, at line 2. I don't know what's wrong with the syntax. Other examples with prepared statements use the same syntax.

CodePudding user response：

Can you try it here : https://dbfiddle.uk/drIJr39p

Is working fine :

CREATE DEFINER=`root`@`localhost` PROCEDURE getData(p_col1 varchar(20), p_col2 varchar(20) )
BEGIN
    
    SET @s = CONCAT('SELECT c1.tara, c2.tara FROM clase c1 JOIN clase c2 ON (c1.clasa > c2.clasa) WHERE c1.',p_col1,'=c2.',p_col1,' AND c1.',p_col2,'=c2.',p_col2);
    -- select @s;
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
end

CodePudding user response：

The problem here seems to be what phpMyAdmin expects from you.

Even if it looks as if you can just enter the codeblock in the definition field and phpMyAdmin would take care of the rest (e.g. add a begin and end if needed), phpMyAdmin requires you to include BEGIN and END around your code if it has more than one statement.

So, in the "definition" input area, just enter

BEGIN
  SET @s=CONCAT('SELECT c1.tara, c2.tara FROM clase c1 JOIN clase c2 
    ON (c1.clasa > c2.clasa) 
    WHERE c1.',p_col1,'=c2.',p_col1,' AND c1.',p_col2,'=c2.',p_col2);
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

The rest is fine.