Can not find the Syntax 1064 Error in my stored procedure using MySQL

user_10375261 0
Viewed 21

I am creating a stored procedure so that I can choose the field of a Table I'm interested in and a second parameter which is "ASC" or "DESC" and the procedure 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 $$
stored-procedures mysql
1 Answers

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.

user_124928900
Related