Calling Stored Procedure with NodeJS and MSSQL

Stored procedures come in different size and shapes; parameters, without parameters, with output parameters, table valued parameters. We will look into these different types of stored procs, and how to interact with them from NodeJS using MSSQL. This article mostly will be a continuation of the previous article which was focused on connection between NodeJS and MSSQL and performing CRUD operations. If you haven't checked already, please checkout the previous article Teaming up with NodeJS and SQL Server.

Without further ado, will move on creating a bunch of different kind of stored procs to understand the how to access, manage and communicate with them from NodeJS. To make things simpler in database end we are considering our table Employee and will create some procs to fetch and save record to and from.

Dealing with Input Parameters

Following query will create a proc named SearchEmployee which help us to find the employees based on their name, the name will be an input parameter to the proc. Nothing fancy in the query, simply using LIKE operator to the needful.

CREATE PROCEDURE [dbo].[SearchEmployee]
	@Name VARCHAR(100)
AS
BEGIN
	SELECT * FROM Employee WHERE LOWER(Name) LIKE '%' + LOWER(@Name) + '%'
END
GO

Now we will make our NodeJS API end point to access this proc as follows:

router.get('/search', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('Name', req.query.name)
            .execute(`SearchEmployee`);
        const employees = result.recordset;

        res.json(employees);
    } catch (error) {
        res.status(500).json(error);
    }
});

Here we have named our API end point as search and we are making use of query parameters available in the request object of the current route. In comparison with our CRUD operations; we are making use of the execute() method instead of query() in order to make a call to proc and also we are using the input() method to pass the value of parameter "Name". Lastly, we are making use of recordset property of the result object.

Finally, we will make a rest call to our "search" API end point in the definitions/employees.rest file as follows:

#### Search Employees
GET http://localhost:3000/api/employees/search?name=jo

Dealing with Output Parameters

Following query will create a proc named GetEmployeesStatus which help us to find status, including their count, max, min, average and sum of salaries of all employees.

CREATE PROCEDURE [dbo].[GetEmployeesStatus]
	@Count		INT OUTPUT,
	@Max		INT OUTPUT,
	@Min		INT OUTPUT,
	@Average	INT OUTPUT,
	@Sum		INT OUTPUT
AS
BEGIN
	SELECT	@Count		= COUNT(1),
			@Max		= MAX(Salary),
			@Min		= MIN(Salary),
			@Average	= AVG(Salary),
			@Sum		= SUM(Salary)	
	FROM Employee;
END
GO

Our NodeJS API end point status with this proc will look like as follows:

router.get('/status', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .output('Count', 0)
            .output('Max', 0)
            .output('Min', 0)
            .output('Average', 0)
            .output('Sum', 0)
            .execute(`GetEmployeesStatus`);
        const status = {
            Count: +result.output.Count,
            Max: +result.output.Max,
            Min: +result.output.Min,
            Average: +result.output.Average,
            Sum: +result.output.Sum
        };

        res.json(status);
    } catch (error) {
        res.status(500).json(error);
    }
});

The output() method of request object will deal with OUTPUT parameter of SQL Server, we simply need to pass the output parameter's value using the output() method. The rest is pretty straight forward, obtaining the output values from result.output property.

Then we will make a rest call to our "status" API end point in the definitions/employees.rest file as follows:

#### Get Employees Status
GET http://localhost:3000/api/employees/status

Dealing with Multiple Record Sets

Stored procs are known for executing multiple queries and returning multiple record set in response. Following query will create a proc named GetSalarySummary which will provide salary summary based on department and job of employees.

CREATE PROCEDURE [dbo].[GetSalarySummary]
AS
BEGIN
	-- get department wise salary summary
	SELECT	
		Department, 
		COUNT(1) EmployeeCount, 
		SUM(Salary) AS Salary, 
		SUM(Salary) * 12 AS Annual
	FROM 
		Employee 
	GROUP BY 
		Department 
	ORDER BY
		SUM(Salary) DESC;

	-- get job wise salary summary
	SELECT
		Job, 
		COUNT(1) EmployeeCount, 
		SUM(Salary) AS Salary, 
		SUM(Salary) * 12 AS Annual 
	FROM 
		Employee 
	GROUP BY 
		Job 
	ORDER BY 
		SUM(Salary) DESC;
END
GO

Our NodeJS API end point summary with this proc will look like as follows:

router.get('/summary', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request().execute(`GetSalarySummary`);
        const summary = {
            Department: result.recordsets[0],
            Job: result.recordsets[1],
        };

        res.json(summary);
    } catch (error) {
        res.status(500).json(error);
    }
});

Here we will make use of recordsets property of result object which will hold 2 sets of arrays in our case, the first array of recordsets will hold salary summary data department wise and the second array will hold salary summary job wise of employees.

We will make a rest call to our "summary" API end point in the definitions/employees.rest file as follows:

#### Employee Summary
GET http://localhost:3000/api/employees/summary

Dealing with Table Valued Parameters

This one is little longer, a table value parameter to a proc is an input parameter which holds the input data in the format of the table, using which we can pass multiple rows of data to proc in order to perform operations on these bunch of input rows. One of such scenario comes when we want to INSERT bulk of records at once. Considering our employee scenario, we will create a proc named AddEmployees which will add multiple employees at once.

For this we will first need to create a user defined table type which act as a type of the input parameter. Shown below:

CREATE TYPE [dbo].[EmployeeType] AS TABLE(
	[Code] [varchar](50) NOT NULL,
	[Name] [varchar](50) NULL,
	[Job] [varchar](50) NULL,
	[Salary] [int] NULL,
	[Department] [varchar](50) NULL
)
GO

After that, our stored proc will look like as follows:

CREATE PROCEDURE [dbo].[AddEmployees]
	@Employees EmployeeType READONLY
AS
BEGIN
	DECLARE @lastId INT;

	SET @lastId = (SELECT MAX(Id) AS LastId FROM Employee);

	INSERT INTO Employee (Code, [Name], Job, Salary, Department)
	SELECT * FROM @Employees;

	SELECT * FROM Employee WHERE Id > @lastId;
END
GO

Here we are adding multiple rows to the table employee we are returning the bunch of newly added employees in response, just in case the user needs to play with the newly generated ids. For this we are creating @lastId variable which denotes the last id present before the bulk insert operation, and then we are simply getting all records those are having ids greater than @lastId.

Coming back to our NodeJS API end point, we have added many to access this proc shown below:

router.post('/many', async (req, res) => {
    try {
        await pool.connect();
        const employeesTable = new mssql.Table();

        employeesTable.columns.add('Code', mssql.VarChar(50));
        employeesTable.columns.add('Name', mssql.VarChar(50));
        employeesTable.columns.add('Job', mssql.VarChar(50));
        employeesTable.columns.add('Salary', mssql.Int);
        employeesTable.columns.add('Department', mssql.VarChar(50));

        const employees = req.body;
        employees.forEach(employee => {
            employeesTable.rows.add(
                employee.Code,
                employee.Name,
                employee.Job,
                employee.Salary,
                employee.Department
            )
        });

        const request = pool.request();
        request.input('Employees', employeesTable);

        const result = await request.execute('AddEmployees');
        const newEmployees = result.recordset;
        res.json(newEmployees);
    } catch (error) {
        res.status(500).json(error);
    }
});

In order to pass table valued parameter to a proc we need to make create instance of mssql.Table class and then add columns to the table, followed by adding the rows to mssql.Table object as shown above. The rest is pretty simple, we are adding this table instance "employeesTable" as input parameter to the procedure call. The recordset thing will work same as previous and we are returning the newEmployees created from the proc execution.

Ending with making a POST rest call to our "many" API end point in the definitions/employees.rest file with JSON data as follows:

#### Add Many Employees
POST http://localhost:3000/api/employees/many
content-type: application/json

[
    {
        "Id": 0,
        "Code": "CT8100",
        "Name": "Bruce Banner",
        "Job": "Salesman",
        "Salary": 20000,
        "Department": "Sales"
    },
    {
        "Id": 0,
        "Code": "CT8200",
        "Name": "Clint",
        "Job": "Salesman",
        "Salary": 20000,
        "Department": "Sales"
    }
]

Irrespective of the tech stack you are dealing with, it is always important to do a justice with stored proc by thoroughly going through these different kinds of scenarios before considering procs as an option in any project. After doing this you will feel yourself in a comfortable position to deal with procs in your project.