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;
GO
Code 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 newRequest
instance representing a single query you want to execute against the database.input()
defines parameters of the query includingStartDate
andEndDate
..execute('
calls theGetAuthorsByBirthDate
')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 start
Code 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/1797
Code language: JavaScript (javascript)
Download the project source code
Download the project source code
Summary
- Use the
execute()
method of theConnectionPool
statement to call a stored procedure in SQL Server from Node.js.