Allows a SQL Stored Procedure to be run when required. Used in conjunction with XL3Link to trigger the execution.
Syntax
XL3RunSQLProc( ExcecuteSQL, Connection, ProcName, [Parameter1],..., [Parameter27])
Parameters
| Parameter | Description |
| ExecuteSQL | A cell reference that contains TRUE when the Stored Procedure should be run. After execution, the cell reference will be set to FALSE or an error message if an error has occurred. |
| Connection | The connection to use to connect to the database. This can either be the Connection ID of a workbook's relational connection or a connection string. |
| ProcName | The Stored Procedure to run |
| [Parameter1],..., [Parameter27] | Optional - The parameter values to use for the Stored Procedure. You can pass up to 27 parameters. |
Examples
XL3RunSqlProc(B3, Variables!C2, C3)
When B3 is set to TRUE using an XL3Link, the stored procedure in C3 is run using the connection string specified in Variables!C2. After completion, B3 is reset to FALSE ready for the next update by an XL3Link. Note that to use this on Anaplan XL Web Edition, the XL3Link should be of type 3 (link with submit changes on Web).
Issues with named parameters
Several data providers (like SqlServer) always use named parameters when running stored procedures. The default names for these are @Parameter1, @Parameter2, and so on.
To avoid wrongly named parameters, you can:
- Wrap your stored procedure in another with the default names, and call that instead.
- Add an Anaplan XL Custom OLEDB or Custom ODBC connection to the workbook, and use that instead. For example, the connection string for OleDb could be
Provider=sqloledb;Data Source=my-Server;Initial Catalog=my-Database;Integrated Security=SSPI
