Azure SQL Database and SQL Server programming with Node.js: Stored procedures, Transactions, and UDFs
This blog will teach you how to write Transactions, Stored Procedures and User Defined Functions (UDF’s) with Azure SQL Database using Node.js. This allows you to write application logic that can be shipped and executed directly on the database
Requirements
It is important that you have followed the previous blog post of this series which helps you setup the Node.js driver.
Stored Procedures
A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database’s command environment, using the exec command.
An example is: exec usp_displayallusers. The name of the stored procedure is “usp_displayallusers”, and “exec” tells SQL Server to execute the code in the stored procedure. (Note: “usp_” in front of the stored procedure name is used to designate this stored procedure as a user-created stored procedure.)
The code inside the stored procedure can be something as simple as: SELECT * FROM USERLIST
This “select” statement will return all data in the USERLIST table. Just save the query and run it when you need to.
Example: Write a simple stored procedure
This Stored Procedure does a SELECT STATEMENT
Step 1: Setup the connection:
var http = require('http');
var sql = require('msnodesql');
var http = require('http');
var fs = require('fs');
var useTrustedConnection = false;
var result = "";
var flag1 = false;
var flat2 = false;
var conn_str = "Driver={SQL Server Native Client 11.0};Server=tcp:yourserver.database.windows.net;" + (useTrustedConnection == true ? "Trusted_Connection={Yes};" : "UID=yourusername;PWD=yourpassword;") + "Database={yourdatabase};";
sql.open(conn_str, function (err, conn)
{
if (err) {
console.log("Error opening the connection!");
return;
}
else
console.log("Successfuly connected");
Step 2: Create the Stored Procedure
conn.queryRaw("CREATE PROCEDURE sp_GetCompanies22 AS BEGIN SELECT [CompanyName] FROM SalesLT.Customer END", function (err, results) {
if (err) {
console.log("Error creating Stored Procedure");
return;
}
});
Step 3 : Execute the Stored Procedure
You can execute the Stored Procedure using the exec statement. We will print the results of the select statement to the screen.
conn.queryRaw("exec sp_GETCompanies22", function (err, results) {
if (err) {
console.log("Error executing Stored Procedure");
return;
}
for (var i = 0; i < 10; i++) {
console.log(results.rows[i]);
result+= results.rows[i] + "\n";
}
});
Step 4: Drop the Stored Procedure
conn.queryRaw("DROP PROCEDURE sp_GETCompanies22", function (err, results) {
if (err) {
console.log("Error dropping Stored Procedure");
return;
}
});
conn.beginTransaction( function( err ) {
if (err) {
console.log("Error dropping Stored Procedure");
return;
}
console.log("Transaction Started");
});
Transactions
A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.
A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.
Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.
Example: Write a simple transaction
Step 1: Begin transaction
We use the sqlsrv_begin_transaction() function to start the transaction
conn.beginTransaction( function( err ) {
if (err) {
console.log("Error dropping Stored Procedure");
return;
}
console.log("Transaction Started");
});
Step 2: Write the queries for the transaction
conn.queryRaw("INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID,OrderQty,ProductID,UnitPrice) VALUES (71774, 22, 709, 33)", function (err, results) {
if (err) {
console.log("Error executing Stored Procedure");
return;
}
flag1 = true;
console.log("Inserted record");
});
conn.queryRaw("UPDATE SalesLT.SalesOrderDetail SET OrderQty = (OrderQty + 103) WHERE ProductID = 709", function (err, results) {
if (err) {
console.log("Error creating Stored Procedure");
return;
}
flag2 = true;
console.log("Updated record");
Commit(conn);
});
});
Step 3: Commit/Rollback the transaction
We do this by using the conn.commit() and conn.rollback() function
function Commit(conn){
// If both queries are successful, commit the transaction otherwise rollbacl.
if(flag2 && flag1){
conn.commit( function( err ) {
if (err) {
console.log("Error dropping Stored Procedure");
return;
}
console.log("Commited record");
});
}
}
User-defined functions
Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
The benefits of using user-defined functions in Azure SQL Database are:
- They allow modular programming.
You can create the function once, store it in the database, and call it any number of times in your program. User-defined functions can be modified independently of the program source code.
- They allow faster execution.
Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
Example: Write a simple UDF
Step 1: Create UDF
We create a complex UDF that returns a table
conn.queryRaw("IF OBJECT_ID(N'dbo.ifGetTotalItems', N'IF') IS NOT NULL DROP FUNCTION dbo.ifGetTotalItems;", function (err, results) {
if (err) {
console.log("Error dropping UDF");
return;
}
});
conn.queryRaw("CREATE FUNCTION dbo.ifGetTotalItems (@OrderID INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT SUM(OrderQty) AS TotalItems FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID GROUP BY SalesOrderID);", function (err, results) {
if (err) {
console.log("Error creating UDF");
return;
}
console.log("Created UDF");
});
Step 2: Use the UDF in a query
conn.queryRaw("SELECT s.SalesOrderID, s.OrderDate, s.CustomerID, f.TotalItems FROM SalesLT.SalesOrderHeader s CROSS APPLY dbo.ifGetTotalItems(s.SalesOrderID) f ORDER BY SalesOrderID;", function (err, results) {
if (err) {
console.log("Error executing UDF");
return;
}
for (var i = 0; i < 10; i++) {
console.log(results.rows[i]);
result+= results.rows[i] + "\n";
}
});