Skip to main content

More About Procedures

Both business services and business operations can use a stored procedure to retrieve data from an external data source, and the approach is the similar regardless of whether you are using a service or operation. After adding EnsLib.SQL.Service.ProcService or EnsLib.SQL.Operation.ProcOperation to your production, use the Procedure setting to specify the name of the procedure, including a ? for each parameter. For example, you could enter Sample.Stored_Procedure_Test(?,?).

Unlike queries, a call to a store procedure can have return values: input parameters can be passed by reference, there can be output parameters, and the entire procedure can return a value. To handle these return values, and differentiate between input and output values, the business service or business operation uses three settings: Parameters (business service) or Input Parameters (business operation) contain values that are passed to the procedure, Output Parameter Names contains property names that are set to values returned in a parameter, and Input/Output identifies whether parameters in the procedure call are input parameters, output parameter, or both.

The Input/Output setting is key to understanding how the business host handles parameters and return values. It accepts three characters that identify the type of parameter: "i" (input), "o" (ouput), and "b" (both/ByRef). The order of these characters within the Input/Output setting corresponds to the order of the parameters of the procedure call. For example, if the procedure call is Sample.Stored_Procedure_Test(?,?,?) and the value of Input/Output is ioo, then the first parameter of the procedure call accepts the value from the Parameters setting, and the second and third parameters are values returned by the procedure. These parameter return values are assigned to the names specified in the Output Parameter Name setting.

In cases where the entire procedure returns a value, the first character of Input/Output should be o and the first name in the Output Parameter Names setting will be assigned the return value.

As an extended example of how this works, suppose your business operation has the following settings:

Setting Value
Procedure Sample.Stored_Procedure_Test(?,?,?,?)
Input Parameters *Value,x
Output Parameter Names ReturnValue,Response,Stream,TruncatedStream
Input/Output oiboo

Notice that the value of Input/Output is greater than the number of question marks in the procedure because there is also a return value. That return value corresponds to the first string in Output Parameter Names. The second value in Input/Output is i because the first parameter in the procedure – the first ? – is an input parameter.  In this case, we are taking that from the Value property of the incoming message. The second parameter is both input and output, meaning that it is passed ByRef. The value x is sent in, but then it also corresponds to the second string in Ouput Parameter Names, so the response message has its Response property set to the value returned in that parameter. The other two parameters are strictly output parameters, so nothing is passed into them, and the return values are assigned to the Stream and TruncatedStream properties of the response message.