I am creating a SP so that I can choose the field of a Table im interested in and a second parameter which is "ASC" or "DESC" and de SP will order the table by that field and also will order it ASC or DESC depending on what the second parameter is. But I can not find the syntax mistake which is a regular 1064 mistake! I am running this in MySQL workbench

DELIMITER $$
CREATE PROCEDURE `PROCEDUREUNO` (IN field VARCHAR(100),IN ordenamiento CHAR(5))
BEGIN
IF field <> ‘’ THEN
SET @orden = concat(‘ORDER BY ’, field,ordenamiento);
ELSE
SET @orden = ‘’;
END IF;
SET @clausula = concat(‘SELECT * FROM Contrataciones ’, @orden);
PREPARE runSQL FROM @clausula;
EXECUTE runSQL;
DEALLOCATE PREPARE runSQL;
END

END $$

CodePudding user response：

You have two errors in your Stored Procedure declaration:

• don't use rounded quotes ‘’, use instead simple quotes ''
• you have two END keywords at the end of your declaration

DELIMITER $$
CREATE PROCEDURE `PROCEDUREUNO` (IN field VARCHAR(100),IN ordenamiento CHAR(5))
BEGIN
    IF field <> '' THEN
        SET @orden = CONCAT('ORDER BY ', field,ordenamiento);
    ELSE
        SET @orden = '';
    END IF;

    SET @clausula = CONCAT('SELECT * FROM Contrataciones', @orden);

    PREPARE runSQL FROM @clausula;
    EXECUTE runSQL;
    DEALLOCATE PREPARE runSQL;
END $$

Check the demo here.