Executing Multi Resultset with NodeJS

Back to: Mastering Node.js and SQL Server

Let’s explore how to execute multi-resultset queries in Node.js with a practical example. We’ll use the mssql package to connect to a SQL Server database and execute a stored procedure that returns multiple resultsets.

Prerequisites:

Before we begin, ensure you have Node.js installed and a SQL Server database set up. Install the mssql package using npm:

npm install mssql

Creating a Stored Procedure:

Let’s assume you have a SQL Server stored procedure named GetMultiResultsets that returns two resultsets. Here’s an example of such a stored procedure:

CREATE PROCEDURE GetMultiResultsets
AS
BEGIN
    -- First Resultset: List of Employees
    SELECT EmployeeID, FirstName, LastName FROM Employees;

    -- Second Resultset: List of Products
    SELECT ProductID, ProductName, UnitPrice FROM Products;
END;

Executing the Multi-Resultset Query:

Now, let’s write Node.js code to execute this stored procedure and handle the multiple resultsets:

const sql = require('mssql');

// Configuration for connecting to SQL Server
const config = {
  user: 'your_username',
  password: 'your_password',
  server: 'your_server_name',
  database: 'your_database_name',
};

(async () => {
  try {
    // Connect to the SQL Server
    await sql.connect(config);

    // Define the SQL statement to execute the stored procedure
    const query = 'EXEC GetMultiResultsets';

    // Execute the query
    const result = await sql.query(query);

    // Access the resultsets
    const resultset1 = result.recordsets[0]; // First resultset
    const resultset2 = result.recordsets[1]; // Second resultset

    // Log or process the resultsets as needed
    console.log('Resultset 1:', resultset1);
    console.log('Resultset 2:', resultset2);
  } catch (err) {
    console.error('Error:', err);
  } finally {
    // Close the SQL Server connection
    sql.close();
  }
})();

Conclusion:

Executing multi-resultset queries in Node.js with SQL Server allows you to efficiently retrieve and work with multiple sets of data from your database. This capability is especially useful in scenarios where you need to fetch and process diverse data sets in a single database call, such as generating reports or aggregating data from different tables.Execute multi-resultset queries in Node.js with SQL Server for efficient data retrieval and processing.

Stay Connected