Error in syntax while writing a procedure to hash password in Sequel Pro

Question

I keep getting a syntax error while trying to create this procedure to hash a password. Not sure why it says there are syntax errors.

The error I keep getting is:

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 '@pFirstName varchar(255), @pLastName varchar(255), @pEmail varchar(255), @' at line 2

CREATE PROCEDURE `Users`
@pFirstName varchar(255),
@pLastName varchar(255),
@pEmail varchar(255),
@pPassword NVARCHAR(50),
@pType int(11),
@pCreatedAt datetime,
@pUpdatedAt datetime,
@pUser_type enum('admin','project-manager','lawyer','anonymous-client','client','external-client','intake-bot'),
@pUndashPassword NVARCHAR(50),
@responseMessage NVARCHAR(250) OUTPUT

AS
BEGIN
SET NOCOUNT ON

BEGIN TRY

    insert into `Users` (`firstName`,`lastName`,`email`, `password`,`type`,`createdAt`,`updatedAt`,`user_type`, `PasswordHash`)
    VALUES(@pFirstName, @pLastName, @pEmail, @pPassword, @pType, @pCreatedAt, @pUpdatedAt, @pUser_type, HASHBYTES('SHA2_512', @pUndashPassword))

    SET @responseMessage='Success'

END TRY
BEGIN CATCH
    SET @responseMessage=ERROR_MESSAGE() 
END CATCH

END

score 1 · Accepted Answer · 2019-12-31T21:44:56.610

When you declare variables used in a SP in MySQL you will need to do it in this way:

STORE PROCEDURE someThing(in Var1 INT, in Var2 VARCHAR(20)....)

I mean you need:

Identify if the element is an input value, an outpu value or maybe both inputouput
Name of the variable
Type of data will be stored in this variable

Now those are the parameters of your SP but if you need variables inside of your SP, the right sintax is:

DECLARE anotherValue INT DEFAULT 10;

I mean you will need:

DECLARE statment
Name of the variable
Type of data will be stored
Optionally you can set a DEFAULT value

Check this example

DELIMITER //
CREATE PROCEDURE EXAMPLE1(IN idUser INT)
BEGIN
    DECLARE Total INT;
    SELECT COUNT(*)
    INTO Total 
    FROM users 
    WHERE users.id = idUser;
    SELECT Total;
END //
DELIMITER ;

After you call it in this way:

CALL EXAMPLE1(1);

Reference

Bill Karwin · Answer 2 · 2019-12-31T21:19:50.777

It looks like you're accustomed to Microsoft SQL Server syntax.

MySQL is not the same implementation of SQL as Microsoft SQL Server. MySQL has its own syntax, which is different. I notice quite a few things in your example that won't work in MySQL. I haven't tested your procedure to see if this is a complete list, so treat this as just a sample of the mistakes.

MySQL does not use the @ sigil for procedure parameters or local variables. See my answer to Mysql stored procedure gives unknown column error when executed
TRY/CATCH is a Microsoft syntax. MySQL does not use that, it uses a syntax for declaring "handlers" for error states: https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html
SET NOCOUNT ON is also Microsoft syntax. MySQL does not have any equivalent feature.
SHA_512() is not a function in MySQL. You should use SHA2(<string>, 512). See https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_sha2

You should read some MySQL documentation about stored procedure syntax.

The manual is for reference. All the syntax is documented, but it's not a task-oriented document, so it's difficult to learn MySQL stored procedures from the manual alone.

There's also an O'Reilly book, which is getting a bit old now, but undoubtedly still helpful, because MySQL stored procedure features haven't changed much: MySQL Stored Procedure Programming .

Error in syntax while writing a procedure to hash password in Sequel Pro

2 Answers2