• Home
• Technical
• Procedures
• Support
• Downloads
• More...

sp_GetConfigValue

Not documented at this time.

@@@---@@@

This SQL script defines and creates the stored procedure [Util].[sp_GetConfigValue]. This is a utility procedure designed to retrieve a configuration value from the [Lookup].[Config] table based on a provided integer key.

Script Breakdown
1. Metadata, Versioning, and Idempotent Drop
The script starts by setting metadata, updating version information, and ensuring idempotency by dropping any existing version of the procedure:

Metadata Declaration:

SQL

DECLARE @Name [varchar](128),@Description [varchar](128),@Version [int],@Type [varchar](5);
SET @Version=240822;
SET @Type='P';
SET @Name='sp_GetConfigValue';
SET @Description='Retrieve the Config Value of a passed Key from the [Config] table';
These lines declare local variables and assign metadata for the sp_GetConfigValue procedure, setting its version to 240822 (August 22, 2024).

EXEC [Deploy].[sp_UpdateEntityVer] @Type=@Type, @Name=@Name, @Description=@Description, @Version=@Version;: This calls [Deploy].[sp_UpdateEntityVer] to record the sp_GetConfigValue procedure's version in the temporary #EntityVers table for the current deployment session. This record will later be persisted to [dbo].[_EntityVersions].

EXEC [Deploy].[sp_DropObject] @Type=@Type, @Name=@Name , @Schema='Util';: This line calls [Deploy].[sp_DropObject] to drop any existing procedure named sp_GetConfigValue in the [Util] schema. This ensures that the CREATE PROCEDURE statement that follows will always create a fresh version, preventing errors if the procedure already exists.

GO: This acts as a batch separator, ensuring the drop operation completes before the CREATE PROCEDURE command is processed.

2. [Util].[sp_GetConfigValue] Stored Procedure Definition
This is the main logic of the procedure:

Parameters:
@ConfigKey [int]: Mandatory. The integer key (from the [Lookup].[Config].[Key] column) of the configuration item whose value is to be retrieved.

@ReturnValue [varchar](128) OUTPUT: Output Parameter. This parameter will hold the retrieved [Value] from the [Lookup].[Config] table.

Logic:
SET NOCOUNT ON;: This statement prevents the return of the count of the number of rows affected by the SELECT statement. This is a common best practice in stored procedures to reduce network traffic and improve performance, especially when only an output parameter is being returned.

SELECT @ReturnValue = [Value] FROM [Lookup].[Config] WHERE [Key] = @ConfigKey: This is the core functionality of the procedure.

It queries the [Lookup].[Config] table.

It selects the [Value] column from the row where the [Key] column matches the @ConfigKey parameter.

The retrieved value is then assigned to the @ReturnValue output parameter.

If no matching ConfigKey is found in the [Lookup].[Config] table, @ReturnValue will be set to NULL.

Purpose and Usage
[Util].[sp_GetConfigValue] provides a simple and encapsulated way for other stored procedures, functions, or application code to retrieve configuration settings from the [Lookup].[Config] table.

Key Use Cases:

Centralized Configuration Access: Allows all parts of the application to consistently read configuration values without directly querying the [Lookup].[Config] table.

Encapsulation: Hides the underlying table structure from consumers of the configuration values. If the [Lookup].[Config] table schema were to change, only this procedure would need modification, not all the code that reads configuration.

Application Behavior Control: Enables dynamic adjustment of application behavior by simply changing values in the [Lookup].[Config] table.

Example Usage (from script comments):

SQL

DECLARE @Value [varchar](128)
EXEC [Util].[sp_GetConfigValue] @ConfigKey=1, @ReturnValue=@Value OUTPUT;

IF @Value IS NOT NULL
BEGIN
PRINT 'Configuration Value for Key 1: ' + @Value;
END
ELSE
BEGIN
PRINT 'Configuration Key 1 not found.';
END
This procedure is a fundamental utility for any application that relies on externalized configuration, making it a valuable part of the dbOmnibus framework.