Node.js SQL Server: Call Stored Procedures

Summary: in this tutorial, you will learn how to call a stored procedure from a database in SQL Server from a Node.js app.

This tutorial begins where the Performing a transaction in Node.js tutorial left off.

We’ll create a new stored procedure in the BookStore database and call it from a Node.js application.

Creating a new stored procedure #

Step 1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server.

Step 2. Create a new stored procedureGetAuthorsByBirthDate that returns authors whose birthdates are within a start date and end date:

CREATE PROCEDURE GetAuthorsByBirthDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        AuthorID,
        FirstName,
        LastName,
        BirthDate
    FROM 
        Authors
    WHERE 
        BirthDate BETWEEN @StartDate AND @EndDate
    ORDER BY 
        BirthDate;
END;
GOCopy

Step 3. Test the stored procedure by executing the following statement:

EXEC GetAuthorsByBirthDate '1775-01-01', '1800-01-01';Copy

Output:

AuthorID | FirstName | LastName | BirthDate
---------+-----------+----------+-------------
3        | Jane      | Austen   | 1775-12-16
20       | Mary      | Shelley  | 1797-08-30
(2 rows)Copy

Calling the stored procedure from Node.js #

Step 1. Create a new file procedure.js within the project directory.

Step 2. Define the findAuthorsByBirthDates function in the procedure.js file, which calls the GetAuthorsByBirthDate stored procedure:

import sql from 'mssql';
import { config } from './config.js';

const findAuthorsByBirthDates = async (startDate, endDate) => {
  try {
    // Connect to the database
    const pool = await sql.connect(config);

    // Call the stored procedure GetAuthorsByBirthDate
    const result = await pool
      .request()
      .input('StartDate', sql.Date, new Date(startDate))
      .input('EndDate', sql.Date, new Date(endDate))
      .execute('GetAuthorsByBirthDate');

    // Return the result
    return result.recordset;
  } catch (err) {
    console.error(err);
  }
};

export { findAuthorsByBirthDates };Copy

How it works.

First, import sql object from mssql package and config object from the config.js module:

import sql from 'mssql';
import { config } from './config.js';Copy

The mssql package provides API to interact with the SQL Server. The config is an object that contains database parameters for the connection.

Second, connect to the database using the provided configuration object:

const pool = await sql.connect(config);Copy

The sql.connect() function returns a ConnectionPool object which allows you to execute queries.

Third, call the stored procedure by using the execute() method of the ConnectionPool object:

const result = await pool
  .request()
  .input('StartDate', sql.Date, new Date(startDate))
  .input('EndDate', sql.Date, new Date(endDate))
  .execute('GetAuthorsByBirthDate');Copy

In this statement:

• pool.request() creates a new Request instance representing a single query you want to execute against the database.
• input() defines parameters of the query including StartDate and EndDate.
• .execute('GetAuthorsByBirthDate') calls the GetAuthorsByBirthDate stored procedure with the input parameters.

Step 3. Modify the index.js file to call the findAuthorsByBirthDates function:

import { findAuthorsByBirthDates } from './procedure.js';

const authors = await findAuthorsByBirthDates('1775-01-01', '1800-01-01');

for (const { FirstName, LastName, BirthDate } of authors) {
  console.log(`${FirstName} ${LastName} - ${BirthDate.toLocaleDateString()}`);
}Copy

How it works.

First, import the findAuthorsByBirthDates function from the procedure.js module:

import { findAuthorsByBirthDates } from './procedure.js';Copy

Second, call the findAuthorsByBirthDates function to find the authors whose birth dates between 1775-01-01 and 1800-01-01:

const authors = await findAuthorsByBirthDates('1775-01-01', '1800-01-01');Copy

Third, display the author’s information including FirstName, LastName, and BirthDate using the for...of statement

for (const { FirstName, LastName, BirthDate } of authors) {
  console.log(`${FirstName} ${LastName} - ${BirthDate.toLocaleDateString()}`);
}Copy

Notice that we use object destructuring in the for...of statement to make the code more concise.

Step 4. Open your terminal and run the following command to execute the index.js file:

npm startCopy

If you see the following output, meaning that the program has successfully called the stored procedure:

Jane Austen - 12/16/1775
Mary Shelley - 8/30/1797Copy

Download the project source code #

Download the project source code

Summary #

• Use the execute() method of the ConnectionPool statement to call a stored procedure in SQL Server from Node.js.

Was this tutorial helpful?

YesNo