Exposing Stored Procedures as REST

Blog Post created by banty01 Employee on Dec 18, 2015

Many companies have COTS (commercial off-the-shelf) applications that use stored procedures to encapsulate business logic.  When they come to Live API Creator and ask about  REST services, they wonder and worry about how they will expose their logic.


First, API Creator reads the schema of most modern databases and creates an ORM for each table, view, and stored procedure.  The Instant REST nature of connecting to SQL makes this a good place to start.  These objects in turn become the default REST endpoints and become the building blocks for Resources (user defined nested documents) and business logic services. This post will explore exposing Stored Procedures as first class citizens as a REST endpoint and calling these procedures in logic rules.


Calling a Stored Procedure using REST

All REST requests look similar in that they are HTTP calls to an API Server.  The URL may vary based on the account and project but the concept of calling a procedure is identical to calling a table or view.  However, the arguments are handled a bit different.  In this example, the procedure get_employee has an argument ‘given_employee_id‘  which is prefaced with ‘arg.’ similar to how C# may use the @ in binding a variable. = 1



The response from a call to a procedure is a bit different in API Creator.  The system will return a JSON response that includes the arguments list, the column meta information, and any row results.  This is helpful to the caller to understand what is being returned.  Since a Sproc may return multiple result sets and multiple rows inside a result.

“arg”: {
“given_employee_id”: 1

columnMeta”: [
“catalog”: “dbdev_demo”,
“schema”: “”,
“tableName”: “PurchaseOrder”,
“columnName”: “order_number”,
“columnLabel”: “order_number”,
“columnType”: “BIGINT”
}, … partial list

“rows”: [
“order_number”: 7,
“amount_total”: 1860,
“paid”: false,
“notes”: “”,
“customer_name”: “Echo Environmental Services”,
“salesrep_id”: 1
}, … partial list

Procedures and Formula

Knowing that a procedure can be called as a REST resource we now combine the power of declarative logic to use these in our formula.  A formula is an expression that returns a computed result.  For example, we may want to create a new order in our order entry system.  We allow our customer to pass in their customer information and item/quantity as a single POST in a nested document.  Our existing system has a series of procedures that are called to create batch headers, order dates, shipping locations, warehouse notifications, order numbers, line sequences, kit part explosions, customer specific product item pricing.

Our legacy code may have been one large block of code or a series of functions calls which bind various user values as arguments to create the various intermediate values.  We already understand how unit testing works – that is – we want to test the smallest unit of work, so in API Creator we create a formula on a table that contains both the user provided values and the system generated values.  These formula will automatically be called in the correct order (much like a spreadsheet determines the correct order of calculations using an internal dependency tree).

Our existing system does not expose a data model that represent something we can expose as a REST API.  So we create a new set of tables that contain only the required attributes (and the attributes we will use as formula to calculate and call our stored procedures).  This model is like an audit record of every POST/PUT/DELETE sent to our system.  This request pattern allows us to add reactive logic rules and events to interact with the base system to insert, update, and delete orders, notify warehouse for shipping, send emails to various actors, etc.


Reactive Logic will automatically order our rules so if we POST a new Order and one or more Items - the system can calculate formula values for the batchNo, OrderNumber, warehouse to ship from, item sequence, price lookup, discounts, availability, kit explosion, etc.  Each of these field values are calculated using values returned from a stored procedure.

A formula would simply call the helper function var result = SysUtilty,getResource(procUrl, params) - the procURL is the name of the procedure and its arguments (shown above).  The result is parsed to get the result.rows[0].attributeName;




Calling a Stored Procedure using GET

One feature that is often overlooked is the Request/Response Event.  Simply create a Resource for each GET stored procedure using type ‘JavaScript‘ and simply return {}; (empty set).  So in this example, we create a custom resource named getCustomerInfo.  Now we create a response event looking for this named resource, call our stored procedure, and extract the response and append it to JSON.

if(req.verb == ‘GET” && req.resourceName == ‘getCustomerInfo’){
var pname = req.getUserProperties().get(“name”);
var url = req.baseUrl + “v1/demo:getCustomer? = ‘”+pname +”‘”;


var response = JSON.parse(SysUtility.restGet(url,null,settings)); = response.result.rows;





Using a REST server to integrate with your existing ERP or SQL backend can be quick and easy.  Adding logic to move your stored procedures to the server in a few lines of JavaScript makes it trivial to extend your existing investment and allow your to build new mobile and web front-ends.