Boost C++ Libraries of the most highly regarded and expertly designed C++ library projects in the world. Herb Sutter and Andrei Alexandrescu, C++ Coding Standards

This is the documentation for an old version of Boost. Click here to view this page for the latest version.

Multi-resultset: stored procedures and multi-queries

Using stored procedures

Stored procedures can be called using the CALL SQL statement. You can use CALL statements from both text queries and prepared statements, in a similar way to other SQL statements. Contrary to other statements, CALL may generate more than one resultset.

For example, given a stored procedure like this:

CREATE PROCEDURE get_employees(IN pin_company_id CHAR(10))
    SELECT id, name, tax_id FROM company WHERE id = pin_company_id;
    SELECT first_name, last_name, salary FROM employee WHERE company_id = pin_company_id;

A statement like CALL get_employees('my_company') will generate three resultsets:

Every resultset contains its own rows, metadata, last insert ID, affected rows and so on.

Calling procedures using the dynamic interface

The same results class we've been using supports storing more than one resultset. You can execute a CALL statement as any other SQL statement:

// We're using the dynamic interface. results can stored multiple resultsets
results result;

// The procedure parameter, employe_id, will likely be obtained from an untrusted source,
// so we will use a prepared statement
statement get_employee_stmt = conn.prepare_statement("CALL get_employees(?)");

// Obtain the parameters required to call the statement, e.g. from a file or HTTP message
std::int64_t employee_id = get_employee_id();

// Call the statement
conn.execute(get_employee_stmt.bind(employee_id), result);

// results can be used as a random-access collection of resultsets.
// returns the matched companies, if any
rows_view matched_company =;

// We can do the same to access the matched employees
rows_view matched_employees =;

// Use matched_company and matched_employees as required

In this context, results can be seen as a random-access collection of resultsets. You can access resultsets by index using results::at and results::operator[]. These operations yield a resultset_view, which is a lightweight object pointing into memory owned by the results object. You can take ownserhip of a resultset_view using the resultset class. For example:

// result is actually a random-access collection of resultsets.
// The INSERT is the 2nd query, so we can access its resultset like this:
boost::mysql::resultset_view insert_result =;

// A resultset has metadata, rows, and additional data, like the last insert ID:
std::int64_t post_id = insert_result.last_insert_id();

// The SELECT result is the third one, so we can access it like this:
boost::mysql::resultset_view select_result =;

// select_result is a view that points into result.
// We can take ownership of it using the resultse class:
boost::mysql::resultset owning_select_result(select_result);  // valid even after result is destroyed

// We can access rows of resultset objects as usual:
std::int64_t num_posts = owning_select_result.rows().at(0).at(0).as_int64();

Calling procedures using the static interface

The static_results class supports operations that return multiple resultsets, too. As with other SQL statements, we need to define the row types in our resultsets in advance:

// Describes the first resultset
struct company
    std::string id;
    std::string name;
    std::string tax_id;
BOOST_DESCRIBE_STRUCT(company, (), (id, name, tax_id))

// Describes the second resultset
struct employee
    std::string first_name;
    std::string last_name;
    boost::optional<double> salary;
BOOST_DESCRIBE_STRUCT(employee, (), (first_name, last_name, salary))

// The last resultset will always be empty.
// We can use an empty tuple to represent it.
using empty = std::tuple<>;

We can now use static_results, passing it as many template arguments as resultsets we expect. The library will check that the correct number of resultsets are actually returned by the server, and will parse them into the row types that we provided:

// We must list all the resultset types the operation returns as template arguments
static_results<company, employee, empty> result;
conn.execute("CALL get_employees('HGS')", result);

// We can use rows<0>() to access the rows for the first resultset
if (result.rows<0>().empty())
    std::cout << "Company not found" << std::endl;
    const company& comp = result.rows<0>()[0];
    std::cout << "Company name: " << << ", tax_id: " << comp.tax_id << std::endl;

// rows<1>() will return the rows for the second resultset
for (const employee& emp : result.rows<1>())
    std::cout << "Employee " << emp.first_name << " " << emp.last_name << std::endl;

Use static_results::rows with an explicit index to access each resultset's data. You can also use explicit indices with the other accessor functions, like static_results::meta and static_results::last_insert_id.

For more information about the static interface, please refer to this section.

Determining the number of resultsets

To know the number of resultsets to expect from a CALL statement, use these rules:

Some examples:

-- Calling proc1 produces only 1 resultset because it only contains statements that
-- don't retrieve data
CREATE PROCEDURE proc1(IN pin_order_id INT, IN pin_quantity INT)
    UPDATE orders SET quantity = pin_quantity WHERE id = pin_order_id;
    INSERT INTO audit_log (msg) VALUES ("Updated order...");
-- Calling proc2 produces 3 resultsets: one for the orders SELECT, one for the
-- line_items SELECT, and one for the CALL statement
CREATE PROCEDURE proc2(IN pin_order_id INT)
    SELECT * FROM orders WHERE id = pin_order_id;
    SELECT * FROM line_items WHERE order_id = pin_order_id;

Output parameters

You can get the value of OUT and INOUT parameters in stored procedures by using prepared statement placeholders for them. When doing this, you will receive another resultset with a single row containing all output parameter values. This resultset is located after all resultsets generated by SELECTs, and before the final, empty resultset.

For example, given this procedure:

CREATE PROCEDURE create_employee(
    IN  pin_company_id CHAR(10),
    IN  pin_first_name VARCHAR(100),
    IN  pin_last_name VARCHAR(100),
    OUT pout_employee_id INT
    INSERT INTO employee (company_id, first_name, last_name)
        VALUES (pin_company_id, pin_first_name, pin_last_name);
    SET pout_employee_id = LAST_INSERT_ID();
    INSERT INTO audit_log (msg) VALUES ('Created new employee...');

You can use:

// To retrieve output parameters, you must use prepared statements. Text queries don't support this
// We specify placeholders for both IN and OUT parameters
statement stmt = conn.prepare_statement("CALL create_employee(?, ?, ?, ?)");

// When executing the statement, we provide an actual value for the IN parameters,
// and a dummy value for the OUT parameter. This value will be ignored, but it's required by the
// protocol
results result;
conn.execute(stmt.bind("HGS", "John", "Doe", nullptr), result);

// Retrieve output parameters. This row_view has an element per
// OUT or INOUT parameter that used a ? placeholder
row_view output_params = result.out_params();
std::int64_t new_employee_id =;

results::out_params simplifies the process.

[Warning] Warning

Due to a bug in MySQL, some OUT parameters are sent with wrong types. Concretely, string parameters are always sent as blobs, so you will have to use field_view::as_blob instead of field_view::as_string.

Semicolon-separated queries

It is possible to run several semicolon-separated text queries in a single connection::execute call. For security, this capability is disabled by default. Enabling it requires setting handshake_params::multi_queries before connecting:

// The username and password to use
boost::mysql::handshake_params params(
    argv[1],                // username
    argv[2],                // password
    "boost_mysql_examples"  // database

// Allows running multiple semicolon-separated in a single call.
// We must set this before calling connect

// Connect to the server specifying that we want support for multi-queries
conn.connect(endpoint, params);

// We can now use the multi-query feature.
// This will result in three resultsets, one per query.
results result;
            title VARCHAR (256),
            body TEXT
        INSERT INTO posts (title, body) VALUES ('Breaking news', 'Something happened!');
        SELECT COUNT(*) FROM posts;

Note that statements like DELIMITER do not work using this feature. This is because DELIMITER is a pseudo-command for the mysql command line tool, not actual SQL.

You can also use the static interface with multi-queries. It works the same as with stored procedures.