MySQL Stored Procedures

Question

Hi, I'm coming from a MS SQL Server background. Working on a new project using MySQL with NaviCat 8 Admin tools. Ok, here's the question. Normally when working in MS land if I want to update some data I use a stored procedure to do this:

Drop Procedure spNew
Create Procedure spNew (@P_Param)

UPDATE Table
SET Field = 'some value'
WHERE ID = @P_Param

I am trying to do this same logic from within NaviCat. I defined the Parameter, (IN '@P_Param' int)

In the Definition i placed:

BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = @P_Param
END;

When I try and save the stored procedure, i'm getting this error: "1064 - You have an error in your SQL syntax, blah, blah, blah"

Can anyone at least point me in the right direction?

Thanks.

Answer 1

CREATE PROCEDURE spNew(P_Param INT)
BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = P_Param;
END;

Note that MySQL syntax and overall ideology are very different from those of SQL Server.

You may also need to set delimiter:

DELIMITER $$

CREATE PROCEDURE spNew(P_Param INT)
BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = P_Param;
END;
$$

DELIMITER ;

BTW, I'm assuming you don't actually call your table "Table", since it's a reserved word.

If you do, you need to enclose it into backticks like this:

DELIMITER $$

CREATE PROCEDURE spNew(P_Param INT)
BEGIN
     UPDATE `Table`
     SET    `Field` = 'some value'
     WHERE  `ID` = P_Param;
END;
$$

DELIMITER ;

Answer 2

Parameters to MySQL stored procedures aren't prefixed with @ or quoted in either the declaration or when used. Local variables are prefixed with @, however.

Try:

DROP PROCEDURE IF EXISTS spNew;
CREATE PROCEDURE spNew(IN P_Param INT)
BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = P_Param
END;