Create a Function - get_no_of_users

• Input: Role ( USER or ADMIN)

    DELIMITER $$

    CREATE
        FUNCTION get_no_of_users(i_role VARCHAR(20))
            RETURNS INT
            DETERMINISTIC
        BEGIN
            DECLARE v_count INT DEFAULT 0;
            SELECT COUNT(*) INTO v_count FROM users WHERE ROLE = i_role;
            RETURN v_count;
        END$$

    DELIMITER ;

• Test

  SELECT get_no_of_users('USER'); -- 3
  SELECT get_no_of_users('ADMIN'); -- 2

Call MySQL Function - test_function.js

    var mysql = require('mysql')
    // Create Connection
    var connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'training_db',
        port:3306
    })

    // Connect to  the MySQL Database
    connection.connect()

    // using query method - pass the query

    let params = ['ADMIN'];
    connection.query('select get_no_of_users(?) as no_of_users', params, function (err, rows) {
    if (err) throw err

    console.log('Result: ', rows[0].no_of_users);
    })

    connection.end()

Test Function

• node test_function.js