Using Parameters

Using Parameters

Stored procedures and SQL codes may get values from the active table, ribbon parameters, named cells, and the current environment.

Below is a list of sources shown in the priority order from highest to lowest:

Parameter NameScopeValue
A parameter with a table column nameAll typesThe parameter gets a value from the column with the same name.
A parameter with a ribbon parameter nameAll typesThe parameter gets a value from the ribbon parameter with the same name.
Note that the ribbon parameters are being synchronized with Excel named cells.
Parameters with predefined names listed below are not shown on the ribbon.
A parameter with a named cell nameAll typesThe parameter gets a value from the named cell with the same name.
A parameter with a parent query parameter nameEvent handlersFor example, an Excel table has the query: EXEC dbo.uspCustomers @ManagerID=101
Event handlers can use a value of the ManagerID parameter.
@WorkbookName or @workbook_nameAll typesThe active workbook name without the directory
@WorkbookPath or @workbook_pathAll typesThe active workbook directory. The value is empty for new workbooks.
@SheetName or @sheet_nameAll typesThe active sheet name
@DataLanguage or @data_languageAll typesA data language code selected using the SaveToDB Options dialog box
@TableName or @table_nameAll typesA qualified name of the active query object like [schema].[name] or "schema"."name"
@EditAction or @edit_actionEdit proceduresINSERT, UPDATE, DELETE, or MERGE
@JsonColumns or @json_columnsEdit proceduresThe parameter gets a JSON array of column names like ["id","name"]
@JsonValues or @json_valuesEdit proceduresThe parameter gets a JSON array of current row values like [1,"abc"]
@JsonValuesF1 or @json_values_f1Edit proceduresThe parameter gets a JSON array of current row values like [1,"abc"]
@JsonValuesF2, or @json_values_f2Edit proceduresThe parameter gets a JSON object of current row values like {"id":1,"name":"abc"}
@JsonChangesF1 or @json_changes_f1Edit proceduresThe parameter gets all changes in JSON arrays.
The add-in executes a single call of the procedure specified in the UPDATE_OBJECT field (or INSERT_OBJECT for the merge mode).
@JsonChangesF2, or @json_changes_f2Edit proceduresThe parameter gets all changes in JSON objects.
The add-in executes a single call of the procedure specified in the UPDATE_OBJECT field (or INSERT_OBJECT for the merge mode).
@ChangedRowCount or @changed_row_countEdit proceduresThe total number of changed rows of the current transaction
@ChangedRowIndex or @changed_row_indexEdit proceduresA row index in the changed rowset of the current transaction starting 1
@EventName or @event_nameEvent handlersActions, ContextMenu, Change, DoubleClick, or SelectionChange
@ColumnName or @column_nameEvent handlersThe active column name
@CellValue or @cell_valueEvent handlersThe active cell value. Use the text data type like nvarchar and convert values to the required data type.
@CellNumberValue or @cell_number_valueEvent handlersThe active cell number value. This value is NULL if the cell has a non-number value.
@CellDateTimeValue or @cell_datetime_valueEvent handlersThe active cell datetime value. This value is NULL if the cell has an invalid datetime value.
@CellAddress or @cell_addressEvent handlersThe active cell address
@CellFormula or @cell_formulaEvent handlersThe active cell formula in the A1-style
@ChangedCellCount or @changed_cell_countEvent handlersThe total number of changed cells in the current transaction
@ChangedCellIndex or @changed_cell_indexEvent handlersA cell index in the changed cell set of the current transaction starting 1
@TransactionID or @transaction_idAll typesA unique GUID of the current transaction
@TransactionStartTime or @transaction_start_timeAll typesThe UTC start time of current transaction
@WindowsUserName or @windows_user_nameAll typesThe Windows login of the current user
@SaveToDbVersion or @savetodb_versionAll typesSaveToDB version
@MergeDate or @merge_dateMerge proceduresThe start time of the operation. Use it to detect that the data present in the latest dataset.
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.