NodeJS with SQL Server Store Procedure
Back to: Mastering Node.js and SQL Server
Let’s explore how to work with SQL Server stored procedures in Node.js with various examples. We’ll use the mssql
package for this demonstration.
Prerequisites:
Before we begin, ensure you have Node.js installed, and you’ve installed the mssql
package using npm:
npm install mssql
Example 1: Calling a Simple Stored Procedure with No Parameters
Assuming you have a simple stored procedure named GetEmployees
that retrieves a list of employees, here’s how to call it:
const sql = require('mssql'); const config = { user: 'your_username', password: 'your_password', server: 'your_server_name', database: 'your_database_name', }; (async () => { try { await sql.connect(config); const result = await sql.query('EXEC GetEmployees'); console.log('Employees:'); console.dir(result.recordset); } catch (err) { console.error('Error:', err); } finally { sql.close(); } })();
Example 2: Calling a Stored Procedure with Input Parameters
If your stored procedure accepts input parameters, you can pass them as arguments:
const sql = require('mssql'); const config = { user: 'your_username', password: 'your_password', server: 'your_server_name', database: 'your_database_name', }; (async () => { try { await sql.connect(config); const firstName = 'John'; const lastName = 'Doe'; const result = await sql.query('EXEC SearchEmployee @FirstName, @LastName', { FirstName: firstName, LastName: lastName, }); console.log('Search Results:'); console.dir(result.recordset); } catch (err) { console.error('Error:', err); } finally { sql.close(); } })();
Example 3: Calling a Stored Procedure with Output Parameters
For stored procedures with output parameters, you can retrieve the output values:
const sql = require('mssql'); const config = { user: 'your_username', password: 'your_password', server: 'your_server_name', database: 'your_database_name', }; (async () => { try { await sql.connect(config); const outputValue = { val: null }; const result = await sql.query('EXEC GetOutputValue @OutputValue OUTPUT', { OutputValue: outputValue, }); console.log('Output Value:'); console.log(outputValue.val); } catch (err) { console.error('Error:', err); } finally { sql.close(); } })();
Example 4: Executing a Transaction with Stored Procedures
When you need to execute a series of stored procedures within a transaction, you can use transactions in Node.js:
const sql = require('mssql'); const config = { user: 'your_username', password: 'your_password', server: 'your_server_name', database: 'your_database_name', }; (async () => { const transaction = new sql.Transaction(); try { await sql.connect(config); await transaction.begin(); // Execute your stored procedures within the transaction const result1 = await transaction.request().query('EXEC Procedure1'); const result2 = await transaction.request().query('EXEC Procedure2'); // Commit the transaction await transaction.commit(); console.log('Transaction completed successfully.'); } catch (err) { // Rollback the transaction on error await transaction.rollback(); console.error('Error:', err); } finally { sql.close(); } })();
Example 5: Calling a Stored Procedure with Multiple Resultsets
Suppose you have a stored procedure named GetSalesData
that returns sales data for different product categories and regions. Here’s how to call it and handle multiple resultsets:
const sql = require('mssql'); const config = { user: 'your_username', password: 'your_password', server: 'your_server_name', database: 'your_database_name', }; (async () => { try { await sql.connect(config); const result = await sql.query('EXEC GetSalesData'); console.log('Product Categories:'); console.dir(result.recordsets[0]); console.log('Sales by Region:'); console.dir(result.recordsets[1]); } catch (err) { console.error('Error:', err); } finally { sql.close(); } })();
Example 6: Calling a Stored Procedure with Table-Valued Parameters
Suppose you have a stored procedure named InsertOrders
that accepts a table-valued parameter to insert multiple orders at once. Here’s how to call it with an array of orders:
const sql = require('mssql'); const config = { user: 'your_username', password: 'your_password', server: 'your_server_name', database: 'your_database_name', }; (async () => { try { await sql.connect(config); // Define an array of orders const orders = [ { OrderID: 101, Product: 'Product A', Quantity: 5 }, { OrderID: 102, Product: 'Product B', Quantity: 10 }, ]; const table = new sql.Table(); table.columns.add('OrderID', sql.Int); table.columns.add('Product', sql.NVarChar(50)); table.columns.add('Quantity', sql.Int); orders.forEach((order) => { table.rows.add(order.OrderID, order.Product, order.Quantity); }); const request = new sql.Request(); request.input('Orders', table); await request.execute('InsertOrders'); console.log('Orders inserted successfully.'); } catch (err) { console.error('Error:', err); } finally { sql.close(); } })();
These additional examples demonstrate more complex scenarios of working with SQL Server stored procedures in Node.js, including handling multiple resultsets and using table-valued parameters. These techniques should cover a wide range of situations you might encounter when interacting with SQL Server databases in your Node.js applications.