Executing Stored Procedures

Executing Stored Procedures

First introduced in Backend Services JavaScript SDK version 1.6.11.
With previous SDK versions, use a RESTful call.

You can execute a Stored Procedure from your code using the invokeStoredProcedure SDK method. It transparently makes the necessary HTTP request to the Stored Procedure's specific endpoint.

It has two parameters: the name of the Stored Procedure and an object containing any parameters that you want to pass to the Stored Procedure.

Response Format

The success response to executing a stored procedure has the following format:

{
    "Result": {
        "OutParameters": { },
        "Data": [ ]
    }
}
  • The OutParameters key is an object that contains the output parameters' names and values. For example:

    {
        "Result": {
            "OutParameters": {
                "@UserId": "enlcl12345"
            },
            "Data": [ ]
        }
    }
    
  • The Data key is an array of arrays of objects, each of which contains a result set returned from the stored procedure. For example:

    {
        "Result":
            {
                "OutParameters":{},
                "Data":[
                    [{...},{...}, ...], // first result set
                    [{...},{...}, ...] // second result set, etc.
                ]
            }
    }
    

When the stored procedure returns an error you still receive a success result. However, it has a different format:

{
  "Result": {
    "HasErrors": true,
    "ErrorMessage": "<error-message-here>"
  }
}

Date and Time Format

The SQL date and time objects are returned in integer format that represents the value of the date column converted to Unix time.

For example, the field LastUpdatedAt in the response below represents a date value.

{  
   "Result":{  
      "OutParameters":{  

      },
      "Data":[  
         [  
            {  
               "LastUpdatedAt":1433931826530
            },
            {  
               "LastUpdatedAt":1226394720000
            }
         ]
      ]
   }
}

You can easily convert the integer to a date in your preferred programming framework.

var dateAsTimestamp = 1234567890000;
var dateFromTimestamp = new Date(dateAsTimestamp);

Examples

This section contains example invocations of stored procedures.

The following examples demonstrate the usage of the invokeStoredProcedure Backend Services JavaScript SDK method. For the full list of supported parameters and options see invokeStoredProcedure in the JavaScript API reference.

Calling a Stored Procedure with Input Parameters

var storedProcedureName = "GetSalesForPeriod";
var inputParams = {
    "@Beginning_Date": new Date(Date.UTC(1998, 0, 1)), // @Beginning_Date: "1998-01-01T00:00:00.000Z"
    "@Ending_Date": new Date(Date.UTC(1998, 11, 31)) // @Ending_Date: "1998-12-31T00:00:00.000Z"
};

everliveApp.businessLogic.invokeStoredProcedure(storedProcedureName, inputParams).then(function(data) {
    if (!data.Result.HasErrors) {
        var result = data.Result;
        var outParameters = result.OutParameters; // a JSON object containing the output parameters
        var resultSet = result.Data[0]; // the first result set

        alert(JSON.stringify(resultSet));
    } else {
        alert("An error occurred while executing the stored procedure: " + data.Result.ErrorMessage);
    }
}, function(err) {
    alert("An error occurred while invoking the stored procedure endpoint: " + err.message);
});

Calling a Stored Procedure without Input Parameters

var storedProcedureName = "GetMostSoldProducts";

everliveApp.businessLogic.invokeStoredProcedure(functionName).then(function(data) {
    if (!data.Result.HasErrors) {
        var result = data.Result;
        var outParameters = result.OutParameters; // a JSON object containing the output parameters
        var resultSet = result.Data[0]; // the first result set

        alert(JSON.stringify(resultSet));
    } else {
        alert("An error occurred while executing the stored procedure: " + data.Result.ErrorMessage);
    }
}, function(err) {
    alert("An error occurred while invoking the stored procedure endpoint: " + err.message);
});      

Using the Result in a Kendo UI Data Source Component

Often you may want to use the result set returned from a stored procedure directly in your app though the Kendo UI Data Source abstraction. To do this, you can use a custom implementation of the transport.read configuration as in this example.

var storedProcedureName = "GetMostSoldProducts";
var inputParams = {
    "@Beginning_Date": new Date(Date.UTC(1998, 0, 1)), // @Beginning_Date: "1998-01-01T00:00:00.000Z"
    "@Ending_Date": new Date(Date.UTC(1998, 11, 31)) // @Ending_Date: "1999-12-31T00:00:00.000Z"
};

var ds = new kendo.data.DataSource({
    transport: {
        read: function(options) {
            everliveApp.businessLogic.invokeStoredProcedure(storedProcedureName, inputParams).then(function(data) {
                if (!data.Result.HasErrors) {
                    var result = data.Result;
                    var resultSet = result.Data[0]; // the first result set

                    // pass the first result set as items of the data source
                    options.success(resultSet);
                } else {
                    options.error(data);
                }
            }, function(err) {
                options.error(err);
            });
        }
    },
    error: function(e) {
        alert(JSON.stringify(e));
    }
});

See Also

Start a free trial Request a demo
Contact us: +1-888-365-2779
sales@telerik.com
Copyright © 2016-2017, Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.