Node.js SQL Server: Calling Stored Procedures with OUTPUT Parameters

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

This tutorial begins where the Calling a stored procedure 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 GetAuthorsByBirthYear that returns authors whose birth years are within the start and end years. The stored procedure has an OUTPUT parameter that stores the number of authors found.

CREATE PROCEDURE GetAuthorsByBirthYear (
    @StartYear SMALLINT,
	@EndYear SMALLINT,
    @AuthorCount INT OUTPUT
) AS
BEGIN
    SELECT 
	AuthorID, FirstName, LastName, BirthDate
    FROM
        Authors
    WHERE
        YEAR(BirthDate) BETWEEN @StartYear AND @EndYear
    ORDER BY
	BirthDate DESC;

    SELECT @AuthorCount = @@ROWCOUNT;
END;Code language: SQL (Structured Query Language) (sql)

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

DECLARE @count INT;

EXEC GetAuthorsByBirthYear
	@StartYear = 1775,
	@EndYear = 1812,
	@AuthorCount = @count OUTPUT;

SELECT @count;Code language: SQL (Structured Query Language) (sql)

Output:

AuthorID | FirstName | LastName | BirthDate
---------+-----------+----------+------------
3        | Jane      | Austen   | 1775-12-16
20       | Mary      | Shelley  | 1797-08-30
9        | Charles   | Dickens  | 1812-02-07
(3 rows)Code language: plaintext (plaintext)

(No column name) 
----------------
3

Calling the stored procedure from Node.js

Step 1. Modify the file procedure.js within the project directory.

Step 2. Define the findAuthorsByBirthYears function in the procedure.js file, which calls the GetAuthorsByBirthYear stored procedure:

// ...

const findAuthorsByBirthYears = async (startYear, endYear) => {
  try {
    // Connect to the database
    const pool = await sql.connect(config);

    // Call the stored procedure GetAuthorsByBirthYear
    const result = await pool
      .request()
      .input('StartYear', sql.SmallInt, startYear)
      .input('EndYear', sql.SmallInt, endYear)
      .output('AuthorCount', sql.Int)
      .execute('GetAuthorsByBirthYear');

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

export { findAuthorsByBirthDates, findAuthorsByBirthYears };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('StartYear', sql.SmallInt, startYear)
  .input('EndYear', sql.SmallInt, endYear)
  .output('AuthorCount', sql.Int)
  .execute('GetAuthorsByBirthYear');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 the input parameters of the statement including StartYear and EndYear.
• output() defines the output parameter of the stored procedure, which is AuthorCount.
• .execute('GetAuthorsByBirthYear') calls the GetAuthorsByBirthYear stored procedure with the input/output parameters.

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

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

const result = await findAuthorsByBirthYears(1775, 1812);
console.log(result.output);Code language: JavaScript (javascript)

How it works.

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

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

Second, call the findAuthorsByBirthYears function to find the authors whose birth year between 1775 and 1812:

const result = await findAuthorsByBirthYears(1775, 1812);Code language: JavaScript (javascript)

Third, display the output parameter AuthorCount:

console.log(result.output);Code language: JavaScript (javascript)

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:

{ AuthorCount: 3 }Code language: JavaScript (javascript)

Download the project source code

Download the project source code

Summary

• Use the output() method of the Request object to get the value of the OUTPUT parameter of a stored procedure.