Call a Stored Procedure

The RunStoredProcedure function passes data between KnowledgeKube and a data source table. Depending on how the expression is written it can be used to add new rows to a table or update existing ones.

The function’s syntax is as follows:

RunStoredProcedure( DataSourceName, ParameterString, ReturnType, ListSeparator, RowSeparator, TimeOut );

The ListSeparator, RowSeparator and TimeOut parameters are optional.

DataSourceName is a string containing the name given to your stored procedure when it was set up as a data source.

ParameterString is a comma-separated string list containing the aliases of each input parameter used by the stored procedure. Output parameters are not required.

ReturnType is an integer that controls what is returned by the stored procedure. This parameter accepts the following arguments:

  • 1 - the procedure does not return anything. Use this option if you want the procedure to perform an action without having to provide an output.
  • 2 - the procedure will return a single value. This value is determined by a return statement inside the stored procedure.
  • 3 - the procedure will return tabular, character-separated values (CSV) containing row data matching the results of your stored procedure. The format of the CSV data is affected by the arguments you supply to the ListSeparator and/or RowSeparator parameters.
  • 4 - the procedure passes the values of its output parameters back to their aliases within your KnowledgeKube model. If the procedure has no output parameters (or those parameters do not have aliases), selecting this option will have no noticeable effect.

ListSeparator is a string that tells the function what character to use as a CSV list separator. Individual data values in each row of your CSV will be separated using this character, which will be a comma unless you specify an alternative.

RowSeparator is a string that tells the function which character to use as a CSV row separator. Complete rows of data in your CSV will be separated using this character, which will be a pipe (|) unless you specify an alternative.

ListSeparator and RowSeparator are only required if you also specify "3" as an argument for the ReturnType parameter.

TimeOut is a string containing a number. This number represents the number of seconds after which the connection will time out, preventing the stored procedure from lagging or even freezing the model due to excessive data or a programmatic error - for example, an infinite loop. If the connection times out before the procedure has finished, an error will occur and nothing will be returned. The default value is 30 seconds, so you should increase this number if you expect your procedure to take longer.

You can use the function's syntax to write the expression manually but there is also the option of using the Data Source Expression wizard to quickly generate the RunStoredProcedure expression.

Before implementing either of these options make sure you have created a Stored Procedure data source, and ensure all of the stored procedure's Parameters that you intend to use have been given aliases.

Use the implementation steps below to automatically generate the RunStoredProcedure function.

Implementation:

  1. Open the Expression Editor.
  2. If the expression already has some content, place the keyboard cursor where you want the new statement to appear.
  3. Right-click anywhere in the main Expression panel.
  4. Select Create Data Source Expression.
  5. Use the Data Source menu to select the stored procedure you want to execute.
  6. Select the Call a Stored Procedure radio button.
  7. Use the drop-down menu just below the radio button to select the type of value you want the procedure to return.

The options in this drop-down menu are as follows:

  • No Results - the procedure will not return anything. Use this option if you want the procedure to perform an action without having to provide an output.
  • A Single Value - the procedure will return a value determined by a return statement inside the stored procedure itself.
  • Return Records as CSV - the procedure will return a string containing tabular, character-separated values (CSV) representing the row data resulting from your stored procedure.
  • Use Output Parameters - the procedure passes the values of its output parameters back to their aliases in your KnowledgeKube model. If the procedure has no output parameters (or those parameters do not have aliases), selecting this option will have no noticeable effect.
  1. Click Next.
  2. Using the check boxes, tick each of the input parameters you want your statement to pass values into.

If you click the check box at the top of the dialog, all of the input parameters associated with the stored procedure can be ticked, or unticked, at once.

  1. Click Next.
  2. If you want to generate additional statements for assigning values to the stored procedure's aliases before writing those values away, select This Data Source Expression Will Populate the Alias Values. Otherwise, you should leave the first radio button selected and ensure that the aliases get their values from elsewhere in your model.

If you do select the second radio button, you'll need to assign an expression to each of the aliases in the list. Each of these expressions will assign its result to the respective alias. You can either type an expression directly into the Value column for an alias, or select the alias and use the text box at the bottom of the dialog to write an expression - using the drop-down menu to pick a standard function if needed - before clicking the Apply button to copy what you've written into the alias table.

  1. Click Finish.
  2. Save the expression before closing the expression editor.