MySQL Syntax Error 1064 – Fixing Error Near IN Stored Procedure

memsqlMySQLstored-proceduressyntax

an aspiring DBA Here.

I'm trying to create a stored procedure for a query I'm running. It is a memsql server which is MySQL based. The procedure is as such:

delimiter //
CREATE PROCEDURE calc_campaign (IN usr VARCHAR(38))
BEGIN

Awesome SELECT statement that uses a variable

END //

The error description is: 

ERROR 1064 (42000): 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 'IN usr VARCHAR(38)) BEGIN my_statement; END' 
at line 1

The syntax I used seems to correspond with this:
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
And with pretty much every other procedure I saw on Stack forums.

What is it I'm missing here? Thanks for any help!

Best Answer

MemSQL is not MySQL - see e.g. their page MySQL Features Unsupported in MemSQL. For details on their stored procedure implementation, please refer to MemSQL's documentation on stored procedures:

[USING database_name] CREATE [OR REPLACE] PROCEDURE procedure_name ( [parameter_list] )
  [RETURNS { data_type [data_type_modifier] } ] AS
  [DECLARE variable_list] [ ...n ]
  BEGIN
    procedure_body
  END ;

parameter_list:
  parameter_name data_type [data_type_modifier [ ...n ] ] [, ...]

data_type_modifier:
  DEFAULT default_value | NOT NULL | NULL | COLLATE collation_name

variable_list:
  variable_name type_expression [= value_expression ] ; [... ;]

As you can see, there is apparently no support for declaring parameters as IN, OUT, or INOUT. (They do however support default values for parameters, something which neither MySQL nor MariaDB support!)

Related Question