Stored procedures, bringing bread and butter for many full stackers. They are the heavy lifters, holds up many business logics, handles input/outputs, returns multiple record sets and tons of more features. Why in this world, one will avoid the communication to happen between the super heroic NodeJS and MSSQL stored procs. In this article we will highlight most of the common scenarios we deal with while calling stored procs 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](http://twitter.com/Name) VARCHAR(100)
AS
BEGIN
SELECT * FROM Employee WHERE LOWER(Name) LIKE '%' + LOWER([@Name](http://twitter.com/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](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](http://twitter.com/Count) INT OUTPUT,
[@Max](http://twitter.com/Max) INT OUTPUT,
[@Min](http://twitter.com/Min) INT OUTPUT,
[@Average](http://twitter.com/Average) INT OUTPUT,
[@Sum](http://twitter.com/Sum) INT OUTPUT
AS
BEGIN
SELECT
[@Count](http://twitter.com/Count) = COUNT(1),
[@Max](http://twitter.com/Max) = MAX(Salary),
[@Min](http://twitter.com/Min) = MIN(Salary),
[@Average](http://twitter.com/Average) = AVG(Salary),
[@Sum](http://twitter.com/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](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](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](http://twitter.com/Employees) EmployeeType READONLY
AS
BEGIN
DECLARE [@lastId](http://twitter.com/lastId) INT;
SET [@lastId](http://twitter.com/lastId) = (SELECT MAX(Id) AS LastId FROM Employee);
INSERT INTO Employee (Code, [Name], Job, Salary, Department)
SELECT * FROM [@Employees](http://twitter.com/Employees);
SELECT * FROM Employee WHERE Id > [@lastId](http://twitter.com/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](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.
Summary
Riding an elephant and working with stored procs feels the same; it requires a good skill to get the most out of it and then making your proc work okay with your backend code is another thing. In this article we have put a spotlight on procs with NodeJS using MSSQL, this will clear most of the common scenarios we face while working with proc from NodeJS backend.
Hope this article helps.
Originally published at https://codeomelet.com.
Top comments (0)