App Settings Configuration — TSQL.APP Guides

Every application needs a handful of values that should not be hard-coded: a sender address, a VAT rate, an export folder, a feature switch. TSQL.APP keeps them in one table in the metadata database, api_settings, and gives you two procedures to use it.

The table

Column	Type	Meaning
`id`	int	Row key
`key`	nvarchar	The setting's name — unique, and what your code asks for
`value`	nvarchar	The value, always stored as text
`enabled`	bit	A disabled setting reads as `NULL`, whatever its value
`for_client`	bit	Marks a setting as meant for the browser client as well

The enabled flag is the useful part. You can switch a setting off without deleting it, and every script that reads it sees NULL and falls back to its own behaviour.

Reading a setting

sp_api_setting_get returns the value, and — this is the convenient bit — creates the setting on first use if it does not exist yet:

DECLARE @VatRate NVARCHAR(MAX);

EXEC sp_api_setting_get
     @key             = N'invoice_vat_rate'
    ,@value           = @VatRate OUT
    ,@default         = N'21'
    ,@default_enabled = 1;

The first time this runs, there is no invoice_vat_rate row, so the procedure inserts one with value 21 and returns 21. From then on it returns whatever is in the table. That means a new setting documents itself: deploy the script, and the setting appears in api_settings ready for an administrator to adjust.

Two parameters decide what that first row looks like:

@default — the value written into the new row.
@default_enabled — whether the new row starts enabled. Leave it at 0 for settings that must be filled in by a person, such as an API key: the row is created disabled, the script reads NULL, and you can tell the user the setting still needs configuring.

Values are text, so convert them where you use them:

DECLARE @VatRate    NVARCHAR(MAX);
DECLARE @VatPercent DECIMAL(5,2);

EXEC sp_api_setting_get @key = N'invoice_vat_rate', @value = @VatRate OUT,
                        @default = N'21', @default_enabled = 1;

SET @VatPercent = ISNULL(TRY_CONVERT(DECIMAL(5,2), @VatRate), 0);

Handling a setting that is not configured

For a value that has no sensible default, create it disabled and check for NULL:

DECLARE @ExportFolder NVARCHAR(MAX);
DECLARE @Message      NVARCHAR(MAX);

EXEC sp_api_setting_get @key = N'export_folder', @value = @ExportFolder OUT;

IF @ExportFolder IS NULL
BEGIN
    SET @Message = N'The setting "export_folder" is not configured yet. Ask an administrator to set it in api_settings.';
    EXEC sp_api_modal_alert @alert_text = @Message;
    RETURN;
END

Writing a setting

sp_api_setting_set updates the value — and enables the row — or inserts it if it is missing:

DECLARE @Today NVARCHAR(MAX) = CONVERT(NVARCHAR(10), GETDATE(), 23);

EXEC sp_api_setting_set @key = N'last_export_date', @value = @Today;

Use it from a settings dialog, or to remember when a scheduled job last ran.

One setting the framework reads itself

Some settings are read by the framework's own procedures. The one to know is environment: until it is prod, sp_api_email redirects every message to the logged-in user rather than the real recipients. When the setting is missing, the first email creates it as test — so production mail is something you switch on deliberately.

Settings or session state?

api_settings holds one value for the whole application. For a value that belongs to one user — the last filter they used, a preferred view — use sp_api_modal_set and sp_api_modal_get instead, which store per-user values. See Parameter Passing Between Actions for when to use which.