Execute

To execute a stored procedure using an ORM class, start with the name of the class and then call the procedure method. When finished, use the execAsync method to run the stored procedure in the database and return the records as a WireBootstrap DataTable.

The following example runs the Ten Most Expensive Products stored procedure in the Northwind database and returns the results as a data table.

const productsTable = await Ten_Most_Expensive_Products.procedure().execAsync();

Note, the execAsync method returns a TypeScript Promise so the await keyword can be used to run the stored procedure asynchronously.

Parameters

To add parameters to the stored procedure, use the param method.

The following example calls the CustOrderHist stored procedure using THEBI as the value for the CustomerId parameter.

const orderTable = await CustOrderHist.procedure()
   .param(CustOrderHistParam.CustomerID, "THEBI")
.execAsync();

The following example executes a query for the first record in the Customers table and then uses the CustomerId value from the record as the parameter when running the CustOrderHist stored procedure.

const customer = await Customers.select().execAsyncFirst();
                
const orderTable = await CustOrderHist.procedure()
    .param(CustOrderHistParam.CustomerID, customer.CustomerID)
    .execAsync();

Typed Results

Stored procedures don't published the schema of their results. However, the ORM entity has a data table interface and object class that allow the fields in the results to be added.

The naming convention for the data table interface is I[entity]DataTable and [entity]Row for an individual result record.

interface ICustOrderHistDataTable extends Omit<IWireDataTable, "Rows"> {
  Rows: Array<CustOrderHistRow>;
}

class CustOrderHistRow {
 
   // add properties here
   
   public myProperty1: string;
   public myProperty2: string;
     
}

Once these properties are set on the Row class, use the execAsyncRows method to return the same data table with each row strongly-typed as Row objects.

const orderTable = await CustOrderHist.procedure().execAsyncRows();
    
orderTable.Rows((order: CustOrderHistRow) => {
    console.log(order.myProperty1);
});