Difference between revisions of "XL3RunSQLProc2"
(→Examples) |
(→See Also) |
||
| (4 intermediate revisions by 2 users not shown) | |||
| Line 5: | Line 5: | ||
==Syntax== | ==Syntax== | ||
| − | {{Code| | + | {{Code|XL3RunSQLProc2( ExcecuteSQL, Connection, ProcName, [Parameter1Name], [Parameter1Value],..., [ParameterNName], [ParameterNValue])}} |
==Parameters== | ==Parameters== | ||
| Line 16: | Line 16: | ||
|- | |- | ||
| {{Code|Connection}} | | {{Code|Connection}} | ||
| − | | The connection to use to connect to the database. This | + | | The connection to use to connect to the database. This must be the Connection ID of a workbook relational connection. |
|- | |- | ||
| {{Code|ProcName}} | | {{Code|ProcName}} | ||
| Line 22: | Line 22: | ||
|- | |- | ||
| {{Code|[Parameter1Name], [Parameter1Value]}} | | {{Code|[Parameter1Name], [Parameter1Value]}} | ||
| − | | The name and value for the parameter to be passed to the stored procedure | + | | The name and value for the parameter to be passed to the stored procedure. You can pass up to 10 pairs. |
|} | |} | ||
| Line 33: | Line 33: | ||
===Multi value parameters=== | ===Multi value parameters=== | ||
| − | If a range is passed as the parameter value, then | + | If a range is passed as the parameter value, then FluenceXL will pass a table variable to the stored procedure. |
All columns will be of type string as Excel columns have no fixed type. If you require int/float/datetime you will need to convert the values in your stored procedure. | All columns will be of type string as Excel columns have no fixed type. If you require int/float/datetime you will need to convert the values in your stored procedure. | ||
| Line 51: | Line 51: | ||
{{Code|XL3RunSqlProc(B3, Variables!C2, C3, "@my_param", D2)}} | {{Code|XL3RunSqlProc(B3, Variables!C2, C3, "@my_param", D2)}} | ||
| − | ''When B3 is set to TRUE (via an [[XL3Link]]) then the Stored Procedure in C3 is run using the connection specified in Variables!C2. A parameter called "@my_param" is passed with a value coming from cell D2. After completion, B3 will be reset to FALSE - ready for the next update by an XL3Link. Note that to use this on | + | ''When B3 is set to TRUE (via an [[XL3Link]]) then the Stored Procedure in C3 is run using the connection specified in Variables!C2. A parameter called "@my_param" is passed with a value coming from cell D2. After completion, B3 will be reset to FALSE - ready for the next update by an XL3Link. Note that to use this on FluenceXL Web Edition, the XL3Link should be of type 3 (HyperLink with submit changes on web).'' |
==See Also== | ==See Also== | ||
| Line 57: | Line 57: | ||
* [[XL3Link]] | * [[XL3Link]] | ||
* [[XL3RunSQL]] | * [[XL3RunSQL]] | ||
| + | * [[XL3TrackChanges]] | ||
| + | * [[XL3Filter]] | ||
| + | * [[XL3Jsonify]] | ||
[[Category:Formulae]] | [[Category:Formulae]] | ||
[[Category:Report Management Formulae]] | [[Category:Report Management Formulae]] | ||
[[Category:Report Management]] | [[Category:Report Management]] | ||
Latest revision as of 16:02, 8 April 2024
Allows a SQL Stored Procedure to be run when required (used in conjunction with XL3Link to trigger the execution)
This function allows named parameters to be passed, and for table parameters to be passed where those are supported (currently SQL Server)
Contents
[hide]Syntax
XL3RunSQLProc2( ExcecuteSQL, Connection, ProcName, [Parameter1Name], [Parameter1Value],..., [ParameterNName], [ParameterNValue])
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 occurred. |
| Connection | The connection to use to connect to the database. This must be the Connection ID of a workbook relational connection. |
| ProcName | The Stored Procedure to run |
| [Parameter1Name], [Parameter1Value] | The name and value for the parameter to be passed to the stored procedure. You can pass up to 10 pairs. |
Parameter values
Single value parameters will be passed using their Excel data type, string or double.
Dates are passed as numbers as that is how Excel stores them. They can be converted to a DateTime using convert(datetime, @numeric_value - 2) The - 2 is required as SQL Server and Excel use different date epochs.
Multi value parameters
If a range is passed as the parameter value, then FluenceXL will pass a table variable to the stored procedure.
All columns will be of type string as Excel columns have no fixed type. If you require int/float/datetime you will need to convert the values in your stored procedure.
All numbers are formatted in en-US format, i.e. the decimal separator is ".".
The table variable type and name are based on the number of columns in the parameter range. For example if the range has three columns the name is XL3ParameterTable3, and is defined as follows:
CREATE TYPE [dbo].[XL3ParameterTable3] AS TABLE( [ParameterValue1] [nvarchar](100) NULL, [ParameterValue2] [nvarchar](100) NULL, [ParameterValue3] [nvarchar](100) NULL )
Examples
XL3RunSqlProc(B3, Variables!C2, C3, "@my_param", D2)
When B3 is set to TRUE (via an XL3Link) then the Stored Procedure in C3 is run using the connection specified in Variables!C2. A parameter called "@my_param" is passed with a value coming from cell D2. After completion, B3 will be reset to FALSE - ready for the next update by an XL3Link. Note that to use this on FluenceXL Web Edition, the XL3Link should be of type 3 (HyperLink with submit changes on web).
