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.