Calling Stored Procedures from the SqlQuery macro.

With the introduction of Smartsite iXperion 1.2, the SqlQuery macro has a new mode to call stored procedures. This new method allows you to call stored procedures from Sxml on SqlServer and Oracle databases using the same Sxml notation.

The new mode can be used by setting the parameter "mode" to "StoredProcedure", put the name of the stored procedure in the parameter "sql" and add the named sql parameters to the macro parameter collection "params". The macro always returns a datatable with two columns. The column "Key" contains the name of the output parameter of the stored procedure, the column "Value" contains the value of the output parameter of the stored procedure. Under SqlServer, stored procedures can have a return value that is not an output parameter. This value is listed in the datatable under the key "_result".

Note that when calling a stored procedure under Oracle that has a single output parameter of the type Ref Cursor (e.g. pr_FriendlyName), the key of the returned value will be "_result" just like in SqlServer.