DEV Community

Discussion on: How To Build Rest API With NodeJS, Express, and MySQL

View post

Replies for: Hey Julia, Thanks for the precise guide. I have the following question, how can I execute a procedure that receives an input parameter? Try the fo...

Collapse
 
juliest88 profile image
Julia Strichash • • Edited

Hey, I guess you used the Create Procedure statement, which I didn't mention in my post.
In any case, if you created a procedure with one parameter, all you need is to use

const result = await query(sql, [value]);
Enter fullscreen mode Exit fullscreen mode
  • The first argument is the SQL query,
  • The second argument is a flat array with all the values in the same order as '?' marks. The value should be simple, like string, int, and so on.

For example:
Create Procedure:

DELIMITER //
CREATE PROCEDURE insert_data(
  IN usrname VARCHAR(25),
  IN pass CHAR(60),
  IN firstname VARCHAR(50),
  IN lastname VARCHAR(50),
  IN useremail VARCHAR(100),
  IN userrole ENUM('Admin', 'SuperUser'),
  IN userage INT(11)
) BEGIN
INSERT INTO
  user (
    username,
    password,
    first_name,
    last_name,
    email,
    role,
    age
  )
VALUES
  (
    usrname,
    pass,
    firstname,
    lastname,
    useremail,
    userrole,
    userage
  );
  END//
DELIMITER ;
Enter fullscreen mode Exit fullscreen mode

Add a method in user.model.js file, which uses the call command:

    create = async ({ username, password, first_name, last_name, email, role = Role.SuperUser, age = 0 }) => {
        const sql = 'CALL insert_data(?,?,?,?,?,?,?)';

        const result = await query(sql, [username, password, first_name, last_name, email, role, age]);
        const affectedRows = result ? result.affectedRows : 0;

        return affectedRows;
    }
Enter fullscreen mode Exit fullscreen mode
Collapse
 
darkonecorp profile image
Darkone Corp •

Thanks Julia for your response and explanation, I could solve my problem.

Collapse
 
darkonecorp profile image
Darkone Corp •

Hi Julia.
I have to execute a procedure that returns an output parameter. How can I get that value ?