{"id":3972,"date":"2024-07-16T14:01:26","date_gmt":"2024-07-16T07:01:26","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3972"},"modified":"2024-07-22T07:55:22","modified_gmt":"2024-07-22T00:55:22","slug":"nodejs-sql-server-call-stored-procedures","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/nodejs-sql-server\/nodejs-sql-server-call-stored-procedures\/","title":{"rendered":"Node.js SQL Server: Call Stored Procedures"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to call a stored procedure from a database in SQL Server from a Node.js app.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0Performing a transaction in Node.js tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

We’ll create a new stored procedure<\/a> in the BookStore<\/code> database and call it from a Node.js application.<\/p>\n\n\n\n

Creating a new stored procedure #<\/a><\/h2>\n\n\n\n

Step 1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server.<\/p>\n\n\n\n

Step 2. Create a new stored procedure<\/a> GetAuthorsByBirthDate<\/code> that returns authors whose birthdates are within a start date and end date:<\/p>\n\n\n

CREATE<\/span> PROCEDURE<\/span> GetAuthorsByBirthDate\n    @StartDate DATE<\/span>,\n    @EndDate DATE<\/span>\nAS<\/span>\nBEGIN<\/span>\n    SET<\/span> NOCOUNT ON<\/span>;\n\n    SELECT<\/span> \n        AuthorID,\n        FirstName,\n        LastName,\n        BirthDate\n    FROM<\/span> \n        Authors<\/span>\n    WHERE<\/span> \n        BirthDate BETWEEN<\/span> @StartDate AND<\/span> @EndDate\n    ORDER<\/span> BY<\/span> \n        BirthDate;\nEND<\/span>;\nGO<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Test the stored procedure by executing the following statement:<\/p>\n\n\n
EXEC GetAuthorsByBirthDate '1775-01-01', '1800-01-01';<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
AuthorID | FirstName | LastName | BirthDate\n---------+-----------+----------+-------------\n3        | Jane      | Austen   | 1775-12-16\n20       | Mary      | Shelley  | 1797-08-30\n(2 rows)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Calling the stored procedure from Node.js #<\/a><\/h2>\n\n\n\n
Step 1. Create a new file procedure.js<\/code> within the project directory.<\/p>\n\n\n\n
Step 2. Define the findAuthorsByBirthDates<\/code> function in the procedure.js<\/code> file, which calls the GetAuthorsByBirthDate<\/code> stored procedure:<\/p>\n\n\n
import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;\n\nconst<\/span> findAuthorsByBirthDates = async<\/span> (startDate, endDate) => {\n  try<\/span> {\n    \/\/ Connect to the database<\/span>\n    const<\/span> pool = await<\/span> sql.connect(config);\n\n    \/\/ Call the stored procedure GetAuthorsByBirthDate<\/span>\n    const<\/span> result = await<\/span> pool\n      .request()\n      .input('StartDate'<\/span>, sql.Date, new<\/span> Date<\/span>(startDate))\n      .input('EndDate'<\/span>, sql.Date, new<\/span> Date<\/span>(endDate))\n      .execute('GetAuthorsByBirthDate'<\/span>);\n\n    \/\/ Return the result<\/span>\n    return<\/span> result.recordset;\n  } catch<\/span> (err) {\n    console<\/span>.error(err);\n  }\n};\n\nexport<\/span> { findAuthorsByBirthDates };<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import sql<\/code> object from mssql<\/code> package and config<\/code> object from the config.js<\/code> module:<\/p>\n\n\n
import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The mssql<\/code> package provides API to interact with the SQL Server. The config<\/code> is an object that contains database parameters for the connection.<\/p>\n\n\n\n
Second, connect to the database using the provided configuration object:<\/p>\n\n\n
const<\/span> pool = await<\/span> sql.connect(config);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The sql.connect()<\/code> function returns a ConnectionPool<\/code> object which allows you to execute queries.<\/p>\n\n\n\n
Third, call the stored procedure by using the execute()<\/code> method of the ConnectionPool<\/code> object:<\/p>\n\n\n
const<\/span> result = await<\/span> pool\n  .request()\n  .input('StartDate'<\/span>, sql.Date, new<\/span> Date<\/span>(startDate))\n  .input('EndDate'<\/span>, sql.Date, new<\/span> Date<\/span>(endDate))\n  .execute('GetAuthorsByBirthDate'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In this statement:<\/p>\n\n\n\n