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 procedure GetAuthorsByBirthDate 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;
GOCode language: SQL (Structured Query Language) (sql)

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

EXEC GetAuthorsByBirthDate '1775-01-01', '1800-01-01';Code language: SQL (Structured Query Language) (sql)

Output:

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

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 };Code language: JavaScript (javascript)

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';Code language: JavaScript (javascript)

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);Code language: JavaScript (javascript)

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');Code language: JavaScript (javascript)

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()}`);
}Code language: JavaScript (javascript)

How it works.

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

import { findAuthorsByBirthDates } from './procedure.js';Code language: JavaScript (javascript)

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');Code language: JavaScript (javascript)

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()}`);
}Code language: JavaScript (javascript)

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 startCode language: JavaScript (javascript)

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/1797Code language: JavaScript (javascript)

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.