CEXECSTOREDPROC¶
The CEXECSTOREDPROC formula is used to execute a Stored Procedure (“Stored Proc”) in a relational database.
The datasource needs to be added to the Stored Proc servers in Server Configuration.
Stored Procedures need to be authorised through Authorised Procs.
Supported relational databases are Microsoft SQL Server, MySQL, and Oracle.
Syntax
CEXECSTOREDPROC (Datasource, StoredProcName, Parameters)
The time the stored procedure was executed is returned as the cell value of the formula.
If an error occurs it is returned as the cell value. You can return an error to the client from inside a stored proc by using RAISERROR. You need to set a severity level greater than or equal to 16.
For example: RAISERROR(‘Proc Raised an Error’,16,1)
Arguments¶
Datasource : string
(required)¶
Specify the relational databases’ datasource name (defined in Server Configuration) that you want to connect to.
StoredProcName : string
(required)¶
Specify the name of the authorised procedure that you want to execute. SQL schemas are allowed in the name.
For example:
- admin.sp_test
- [admin].sp_test
Parameters : string
¶
Specify the Stored Procedures’ parameters.
For example: “@param1=’Hello’,@param2=’world’, @ID=22”
If the parameter @loginId is defined in the stored proc, the logged in user id will be passed to the procedure.
If you need to reference a string of parameters in another excel cell you’ll need to escape the first @ symbol with ‘.
For example: ‘@param1=’Hello’,@param2=’world’, @ID=22
Usage Scenarios¶
This formula is used when you want to execute an authorised stored procedure and not worry about any return information except success or failure. One of the most common usage scenarios for this is where you have an Administration console for your CALUMO implementation that allows users to re-process the cube or re-run ETL’s on demand.
You can also use CEXECSTOREDPROC for:
- Triggering SQL jobs
- Sending an email using database mail.
- Processing a cube, dimension or measure group.
- Modifying captions of cube members (by updating the source tables)
Examples¶
=CEXECSTOREDPROC("myDS","test_proc","@param1=123,@param2='Hello World'")