Configuring Parameter Value Lists

Configuring Parameter Value Lists

The SaveToDB add-in places parameters of stored procedures, SQL codes, HTTP queries, and text file queries on the ribbon.

Users can change parameter values, and the add-in executes the queries with the new values.

By default, a user can specify any value of appropriate data type. User's values are stored in a history.

Also, users can select fields of tables and views to use in the WHERE clause of the query. Such fields are placed on the ribbon also.

By default, the field parameters have value lists as unique column values, and users can select the existing values only.

Database developers may define tables, views, stored procedures, functions, or SQL codes used to populate parameter values.

In this case, users may select values from the list only.

This feature can be used to prevent non-indexing queries from big data tables and views.

The database objects used to select values can have parameters also. So, the second parameter value list can depend on the first parameter value and so on.

You may use this for working with hierarchy data, for example.

To configure this feature, just install SaveToDB Framework to your database using the Application Installer.

Configuration Specification

SaveToDB 8 supports configuration versions 5 and 8 and does not support version 3.

Version 5

SaveToDB reads the configuration from tables and views that contain the following fields:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. PARAMETER_NAME
  5. SELECT_SCHEMA *
  6. SELECT_NAME
  7. SELECT_TYPE
  8. SELECT_CODE

* The add-in does not use the TABLE_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

This is the latest version of SaveToDB 7. You may continue using it with existing applications.

Version 8

SaveToDB 8 allows using the event handler configuration to configure parameter value lists:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. COLUMN_NAME
  5. EVENT_NAME
  6. HANDLER_SCHEMA *
  7. HANDLER_NAME
  8. HANDLER_TYPE
  9. HANDLER_CODE
  10. TARGET_WORKSHEET
  11. MENU_ORDER
  12. EDIT_PARAMETERS

* The add-in does not use the TABLE_SCHEMA and HANDLER_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

This configuration table has all the required fields with other prefixes: COLUMN_ instead of PARAMETER_ and HANDLER_ instead of SELECT_.

So, we decided to unify event handler and parameter configurations and use the single table in SaveToDB 8.

Specify the ParameterValues value in the HANDLER_TYPE field for parameter value queries.

Field Description

TABLE_SCHEMA and TABLE_NAME define a customizable database or code-based object.

PARAMETER_NAME defines a parameter or a field of the customizable object.

SELECT_SCHEMA, SELECT_NAME, SELECT_TYPE, and SELECT_CODE define the object used to select parameter values.

The SELECT_TYPE column may contain the following types:

  • PROCEDURE
  • FUNCTION
  • TABLE
  • VIEW
  • CODE
  • RANGE
  • VALUES

Use the first four for existing database objects (stored procedures, functions, tables, and views).

Specify schemas and names for such objects in the SELECT_SCHEMA and SELECT_NAME fields.

Other types (CODE, RANGE, and VALUES) are described below.

Specify schemas and names that do not conflict with the existing database objects.

Customizing Queries from Tables and Views

You may customize queries from tables and views using the SELECT_CODE field.

You may specify comma-separated field names to select.

For example:

id, name

Also, you may use the following modifiers as field name prefixes:

+ORDER BY ASC
-ORDER BY DESC
@WHERE

For example, if you add the following code to the SELECT_CODE field for the dbo.view_subcategories view:

id,+name,@category

The add-in will generate the following SQL code:

SELECT [id], [name] FROM [dbo].[view_subcategories] WHERE [category] = @category ORDER BY [name]

As you may see, this is a light-way alternative to using SQL codes described below.

Using SQL Code

Place an SQL code into the SELECT_CODE field.

The SQL code can contain parameters in the following formats:

  • @ParameterName for Microsoft SQL Server, Microsoft SQL Server Compact, and SQLite.
  • :ParameterName for Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, Snowflake, and PostgreSQL.

This is an example of the SQL code:

SELECT DISTINCT CategoryID, CategoryName FROM dbo.Category c WHERE c.ParentCategoryID = @CategoryID

In this example, the add-in loads parameter values every time when the user changes the CategoryID parameter.

Using Ranges

You may specify a range as a parameter value source.

Specify a range or a named cell name in the SELECT_CODE field.

Using Values

This is a simple case.

Just specify parameter values separated by commas or semicolons in the SELECT_CODE field.

Add an empty value in the first position, if you need to have it in the ribbon parameter list.

Dependent Parameters

For example, we have a procedure with the following parameters: CategoryID, SubcategoryID, and BrandID.

We may create three procedures to populate ribbon parameter values:

CREATE PROCEDURE [xls12].[uspParameterValues_CategoryID]
CREATE PROCEDURE [xls12].[uspParameterValues_SubcategoryID]
    @CategoryID int = NULL
CREATE PROCEDURE [xls12].[uspParameterValues_BrandID]
    @CategoryID int = NULL
    , @SubcategoryID int = NULL

In this example, when a user changes the CategoryID parameter, then the add-in executes stored procedures to get the SubcategoryID and BrandID parameter values dependent on the CategoryID value.

Output Values

Value list queries may return one, two, or more than two columns.

Single-Column Output

The add-in uses values as is.

Two-Column Output

The add-in uses the first column for parameter values and the second column for value names.

This is a common case when the parameter requires id values while users work with meaningful names.

Multi-Column Output

You may try to use this feature also.

The add-in detects columns to use as ids and names.

However, you may return two columns to solve the task easily.

Empty Values

The add-in adds an empty value (NULL) to dependent parameter value lists only (SubcategoryID and BrandID in the example above).

For independent parameters, it uses values returned by the query only.

You may use the code like below as the first line of your code to add the empty value:

SELECT NULL AS ID, NULL AS Name UNION

Creating Database Configuration Objects

You may create the configuration tables or views yourself.

This is useful when your application must create the configuration dynamically.

In other cases, just install SaveToDB Framework to your database using the Application Installer.

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.