Trying to create a stored procedure with 2 inserts

Question

I am very new to stored procedures. I am trying to create a stored procedure that inserts records into two different tables, but I am having issues.

I am coding in C# with .NET connector to MySQL.

I have two tables, prop_details and prop_account. prop_details table has an auto increment column (id) and prop_account table defines a foreign key prop_id that matches prop_details.id.

Following pseudo code explains what the stored procedure is supposed to do:

insert form elements into table prop_details
insert relationship of prop_details.id into table prop_account

Here is the stored procedure that I tried to create

DROP PROCEDURE IF EXISTS `InsertPropertybyClientID`;

CREATE DEFINER = `root`@`localhost` PROCEDURE `InsertPropertybyClientID`(IN 'in_account' int(11), IN `in_title` varchar(100), IN 'in_type' int(2), IN 'in_active' tinyint(1), IN 'in_created' datetime)
BEGIN
INSERT INTO prop_details 
    (prop_title, prop_type, prop_active)
VALUES 
    (in_title, in_type, in_active, in_created);

INSERT INTO prop_account
    (prop_id, acnt_id)
VALUES
    (LAST_INSERT_ID(), in_account);
END;

When trying to save the stored procedure, I am getting this error

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 "'in_account' int(11), IN 'in_title' varchar(100), IN 'in_type' int(2), IN 'in_ac' at line 1

What am I doing wrong?

Answer 1

Actually the error details express what is wrong:

near "'in_account' int(11), IN 'in_title' varchar(100), IN 'in_type' int(2), IN 'in_ac' at line 1

In MySQL reference documentation on identifiers, it states that:

The identifier quote character is the backtick (“`”):

and

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks:

This means that unless you enable ANSI_QUOTES SQL mode, you should not use single or double quotes to represent identifiers. You can either user backtick (`) or you can omit backtick if the word you are using is not a reserved word.