Difference between revisions of "XL3RunSQLProc"

(Issues with named parameters)
(See Also)
Line 43: Line 43:
* [[XL3Link]]
* [[XL3Link]]
* [[XL3RunSQL]]
* [[XL3RunSQL]]
* [[XL3RunSQLProc2]]
[[Category:Report Management Formulae]]
[[Category:Report Management Formulae]]
[[Category:Report Management]]
[[Category:Report Management]]

Revision as of 09:47, 24 August 2020

Allows a SQL Stored Procedure to be run when required (used in conjunction with XL3Link to trigger the execution)


XL3RunSQLProc( ExcecuteSQL, Connection, ProcName, [Parameter1],..., [Parameter27])


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 occurred.
Connection The connection to use to connect to the database. This can either be the Connection ID of a workbook relational connection or a connection string.
ProcName The Stored Procedure to run
[Parameter1],..., [Parameter27] Optional - The parameter values to use for the Stored Procedure


XL3RunSqlProc(B3, Variables!C2, C3)

When B3 is set to TRUE (via an XL3Link) then the Stored Procedure in C3 is run using the connection string specified in Variables!C2. After completion, B3 will be reset to FALSE - ready for the next update by an XL3Link. Note that to use this on XLCubed Web Edition, the XL3Link should be of type 3 (HyperLink with submit changes on web).

Issues with named parameters

Some data providers (e.g. SqlServer) always use named parameters when executing stored procedures, and the default names for these are @Parameter1, @Parameter2 etc.

To avoid wrongly named parameters you can:

  • Wrap your stored proc in another with the default names, and call that instead
  • Add an XLCubed "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

See Also