0

I am switching from ms sql to my sql , but i am getting a error for a simple order by statement. Cannot figure out whats wrong here .

Error message is "Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 11"

create  PROCEDURE usp_get_orders
(
in p_search_text varchar(255),
out p_exit_status int 
)
BEGIN
    
    
     select * 
     from orders   
     order by order_number desc;


  set p_exit_status = 1;
   
END;
Kuntady Nithesh
  • 11,371
  • 20
  • 63
  • 86

1 Answers1

1

This is most likely a delimiter problem.

In MySQL, you need to define a custom delimiter other than ; to disambiguate instructions that should be executed in the procedure from instructions to really execute (this is well explained in this answer for "Delimiters in MySQL".

The whole code for your procedure should be something like this:

/* Set a custom delimiter other than ; */
DELIMITER $$

create  PROCEDURE usp_get_orders
(
in p_search_text varchar(255),
out p_exit_status int 
)
BEGIN
    
     select * 
     from orders   
     order by order_number desc;


  set p_exit_status = 1;
   
/* Use the custom delimiter to end procedure creation */
END$$

/* Reset the delimiter to the default ; */
DELIMITER ;
delvalt
  • 79
  • 4