Defining Stored Procedures

Stored procedures are function-like interfaces to the data source that can be used to search, modify, or delete data. Stored procedures model actions that typically cannot be represented as SELECT, INSERT, UPDATE, or DELETE statements. Modeling stored procedures is a similar process to modeling tables in that you can use the same built-in data processing operations to implement stored procedures.

Stored Procedure Schemas vs. Table Schemas

With minor modifications, you can follow the same process to create a stored procedure that you would follow to add support for INSERT, UDPATE, and DELETE statements: define stored procedures in .api files, which, like .rsd files, consist of an info block and scripts that call data processing operations.

Instead of columns, the info block defines the input and output parameters of the stored procedure. Instead of the attr element, define inputs with the input element in the info block.

As with other SQL statements, when the stored procedure is executed, the _input item contains the input parameters. You can use the _input item to map the stored procedure inputs to the operation inputs.

As with table schemas, you can use the info block to process the response. Describe the outputs of a stored procedure with the output attribute in the info block. In the output attributes, you can specify the XPaths to extract nodes of hierarchical data.

Example Stored Procedure

The following stored procedure retrieves a Person record given their Id. This fully functional schema shows how to build the request and use XPaths to parse the response.

A stored procedure executes the GET method of the schema. Build the API request in this method: check that required inputs were provided and alert the user otherwise with the api:validate keyword. Use Value Formatters to simplify working with strings, dates, and math expressions.

Invoke the operation with the api:call keyword. To return data, insert the api:push keyword in the scope of the operation call.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <api:info title="GetODataPersonById" description="Retrieves the OData Person specified by Id.">
    <output name="ID"             other:jsonPath="/" />
    <output name="EmployeeID"     other:jsonPath="/json/EmployeeID" />
    <output name="Name"           other:jsonPath="/json/Name" />
    <output name="TotalExpense"   other:jsonPath="/json/TotalExpense" />
    <output name="HireDate"       other:jsonPath="/json/HireDate" />
    <output name="Salary"         other:jsonPath="/json/Salary" />  
  </api:info>
  
  <api:set attr="uri" value="http://services.odata.org/V4/OData/(S(5cunewekdibfhpvoh21u2all))/OData.svc/Persons" /> 
  
  <!-- The JSONPath attribute of the schema splits the JSON into rows based on elements that repeat at the same level. -->
  <api:set attr="JSONPath" value="/json/" />

  <!-- See the jsonproviderGet page in the Operations subchapter to set any needed HTTP parameters. -->
  <api:set attr="ContentType"   value="application/json" />

  <!-- The GET method corresponds to EXECUTE. Within the script block, you can see the URI modified to append a query string parameter. The results of processing are pushed to the schema's output.  -->
  <api:script method="GET">
    <api:set attr="method" value="GET"/>
    <api:set attr="uri" value="[uri]([_input.Id])?$format=json"/>
    <api:call op="jsonproviderGet">
      <api:push />
    </api:call>
  </api:script>
	
</api:script>

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

ODBC Driver for JSON

Defining Stored Procedures

Stored Procedure Schemas vs. Table Schemas

Example Stored Procedure

Keyword Reference