Use is not valid input at this position MySQL Workbench

Question

I'm trying to create a stored procedure in MySQL Workbench:

USE `data_p`;
DROP PROCEDURE IF EXISTS `Product`;
DELIMITER $$
USE `data_p`$$
CREATE DEFINER=`data_s_admin`@`...` PROCEDURE `Product`(

  INOUT d_id INT,
  INOUT d_fk_c INT,
  INOUT d_s VARCHAR(255),
  INOUT d_p DECIMAL,
  INOUT d_c_a DATE,
  INOUT d_o_p DECIMAL,
  INOUT d_s_c DECIMAL,
  INOUT d_x_s VARCHAR(20),
  INOUT d_d_w VARCHAR(20),
  INOUT d_fk_c_a INT,
  INOUT d_s enum('ac','in','de')
)

BEGIN
    DECLARE cs_id INT;
    DECLARE cs_fk_c INT;
    DECLARE cs_s VARCHAR(255);
    DECLARE cs_p DECIMAL;
    DECLARE cs_c_a DATE;
    DECLARE cs_o_p DECIMAL;
    DECLARE cs_s_c DECIMAL;
    DECLARE cs_x_s VARCHAR(20);
    DECLARE cs_d_w VARCHAR(20);
    DECLARE cs_fk_c_a INT;
    DECLARE cs_s enum('ac','in','de');

 SELECT 

   d_id ,
   d_fk_c ,
   d_s,
    d_p ,
   d_c_a ,
   d_o_p ,
   d_s_c ,
   d_x_s,
   d_d_w,
   d_fk_c_a,
   d_s
 )
 INTO 

  cs_id,
  cs_fk_c,
  cs_s,
  cs_p,
  cs_c_a,
  cs_o_p,
  cs_s_c,
  cs_x_s,
  cs_d_w,
  cs_fk_c_a,
  cs_s

 FROM 

        data_p.cas
 WHERE 
        cs_s = d_s AND cs_s = 'ac';

 END $$
 DELIMITER ;

When i click on data_s==> stored procedures, i create new stored procedure and then i paste this code, my errors are : Syntax error: 'USE' is not valid input at this position Syntax error: IF : unexpected at this position

What is the problem? i really want to fix this to know how many rows affected

I create my stored procedure in database 'data_s' and i return all data from 'data_p '

Can anyone help me please.

Many thanks for any help

Solution 1

Although it is an old question, didn't saw the answer, but I manage to solve a same problem.

It is possible that the file was save as UTF8 with BOM (or the content was copied from such a file).

In this case, try to save the file in UTF8 format (without BOM) and then re-run the code.

For me it fix a same problem.

Note: 1) You can change the encoding, for example, with Notepadd++. 2) Note that BOM is acronyms for Byte-Order-Mark, which is a short sequence of bytes in the beginning of a file that marks the encoding of the file.

Solution 2

When you use the object editor (which you get when you select "Create Stored Procedure..." in the context menu of the schema tree) you don't need to add all the decoration. Simply write your stored procedure without USE, DELIMITER and DROP. When you apply your changes you will see that MySQL Workbench automatically creates a USE command for you (depending on which schema you clicked to trigger the SP editor). You should drop an existing SP manually before you create it again.

If you use the SQL editor instead then you need all the decoration, however.