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

sp_UpdateConfig

Not documented at this time.

@@@---@@@

This SQL script defines and creates the stored procedure [Util].[sp_UpdateConfig]. This utility procedure is designed to either insert a new configuration record or update an existing one in the [Lookup].[Config] table. It provides granular control over which fields are updated based on the provided parameters.

Script Breakdown
1. Metadata, Versioning, and Idempotent Placeholder Creation
The script begins with the standard pattern for deploying procedures within this framework:

Metadata Declaration:

SQL

DECLARE @Name [varchar](128), @Description [varchar](128), @Version [int], @Type [varchar](5);
SET @Version=240821;
SET @Type='P';
SET @Name='sp_UpdateConfig';
SET @Description='Update or Insert records in the [Config] table';
These lines set the metadata for the sp_UpdateConfig procedure, including its version (240821).

EXEC [Deploy].[sp_UpdateEntityVer] @Type=@Type, @Name=@Name, @Description=@Description, @Version=@Version;: This call records the procedure's version in the temporary #EntityVers table, which will later be persisted.

EXEC [Deploy].[sp_CreateEmptyObject] @Type=@Type, @Name=@Name, @Schema='Util';: This ensures that a placeholder for sp_UpdateConfig exists in the [Util] schema. This is a robust way to ensure the subsequent ALTER PROCEDURE statement will succeed, whether the procedure is being created for the first time or updated.

GO: Separates the setup commands from the procedure definition.

2. [Util].[sp_UpdateConfig] Stored Procedure Definition
This is the main logic for updating or inserting configuration records:

Parameters:
@Id [int]: Mandatory. The ID of the configuration record. This procedure explicitly uses IDENTITY_INSERT to allow the caller to specify the ID for insertion or to identify the record for update.

@Key [int]=0: Optional. The integer key of the configuration item. Defaults to 0. If 0, it won't be updated in an existing record.

@Key_Desc [varchar](255)='': Optional. The description of the configuration key. Defaults to an empty string. If empty, it won't be updated.

@Value [varchar](MAX)='NONE': Optional. The actual configuration value. Defaults to 'NONE'. If 'NONE', it won't be updated.

@AddOnly [bit]=0: Optional. If 1, the procedure will only attempt to INSERT a new record. If a record with @Id already exists, it will RETURN without updating. Defaults to 0.

@Advanced [bit]=0: Optional. A flag to designate the configuration item as "advanced." Used only during INSERT.

Internal Variables:
@SqlCode [varchar](1024): Used to build the dynamic SQL UPDATE statement. Note that varchar(1024) might be too small if @Value (which is varchar(MAX)) were to be included dynamically in its entirety. However, since @Value is concatenated as a literal, it means @Value itself cannot contain single quotes unless properly escaped, and the total length of @SqlCode could exceed 1024 characters. nvarchar(MAX) is generally safer for dynamic SQL.

@NSqlCode [nvarchar](1024): Used to cast @SqlCode to nvarchar before execution. This is good practice for EXEC. The same length limitation applies.

@AddComma [bit]: A flag used to manage comma placement when building the dynamic UPDATE statement.

Logic:
SET NOCOUNT ON;: Suppresses row count messages.

SET IDENTITY_INSERT [Lookup].[Config] ON;: This crucial statement allows explicit values to be inserted into the ID identity column. It must be ON before inserting records where the ID is provided and must be OFF afterward.

Check for Existing Record:

IF NOT EXISTS (SELECT * FROM [Lookup].[Config] WHERE ID = @Id): Checks if a record with the given @Id exists.

INSERT Block: If no record exists, it inserts a new row into [Lookup].[Config] using all provided parameters (@Id, @Key, @Key_Desc, @Value, @Advanced).

ELSE (Update Block): If a record does exist:

IF @AddOnly = 1 RETURN;: If @AddOnly is 1, the procedure immediately exits without performing any update. This enforces an "insert-only" behavior for existing IDs.

Dynamic UPDATE Statement Construction:

Starts building the UPDATE statement: SET @SqlCode = ' UPDATE [Lookup].[Config] SET '.

Conditional Field Updates: It conditionally adds [Key], [Key_Desc], and [Value] to the SET clause based on their default "empty" or "not provided" values (0, '', 'NONE').

@AddComma logic is used to correctly place commas between updated fields.

Concatenation Vulnerability: The direct concatenation of @Key_Desc and @Value into the dynamic SQL SET @SqlCode = @SqlCode + ' [Key_Desc]=''' + @Key_Desc + '''' and SET @SqlCode = @SqlCode + ' [Value]=''' + @Value + '''' is vulnerable to SQL Injection if @Key_Desc or @Value parameters can contain single quotes that are not properly escaped by the caller. Using QUOTENAME() for string values or sp_executesql with proper parameterization would be much safer.

Commented Email Validation: A commented-out block shows a placeholder for future email validation, indicating a potential enhancement.

SET @SqlCode = @SqlCode + ' WHERE [ID]=' + CAST(@Id AS [varchar](5));: Completes the UPDATE statement with the WHERE clause.

Execution:

SET @NSqlCode = @SqlCode;

EXEC (@NSqlCode);: Executes the dynamically constructed UPDATE statement.

SET IDENTITY_INSERT [Lookup].[Config] OFF;: This line is essential to turn off IDENTITY_INSERT after the operation, returning the table to its normal identity behavior. This must always be done to avoid affecting other operations on the table.

Purpose and Usage
[Util].[sp_UpdateConfig] is a core CRUD (Create, Read, Update, Delete) utility for managing application configuration within the database.

Centralized Configuration Management: Provides a controlled way to add or modify configuration settings.

Flexible Updates: Allows updating only specific fields of a configuration record by ignoring default parameter values.

Idempotent Operations: The IF NOT EXISTS for insert and IF @AddOnly = 1 for update help make the procedure robust to repeated calls.

Data Integrity: Requires explicit ID values and uses IDENTITY_INSERT, which demands careful handling by the caller.

Example Usage (from script comments):

Insert a new config item (if ID 1 does not exist):

SQL

EXEC [Util].[sp_UpdateConfig] @Id = 1, @Key = 100, @Key_Desc = 'Application Name', @Value = 'My Awesome App', @Advanced = 0;
Update an existing config item (if ID 1 exists, and @AddOnly is not 1):

SQL

EXEC [Util].[sp_UpdateConfig] @Id = 1, @Value = 'Updated App Name';
Attempt to add, but do not update if exists:

SQL

EXEC [Util].[sp_UpdateConfig] @Id = 1, @Key = 100, @Key_Desc = 'Application Name', @Value = 'My App', @AddOnly = 1; -- If ID 1 exists, this will do nothing.
Potential Enhancements/Considerations:

SQL Injection Vulnerability: The direct string concatenation for @Key_Desc and @Value is a security risk. This should be refactored to use sp_executesql with parameterized queries.

varchar(MAX) in dynamic SQL: If @Value were truly MAX length and concatenated, it could easily exceed the varchar(1024) limit of @SqlCode and @NSqlCode. This reinforces the need for nvarchar(MAX) for the dynamic SQL variables and parameterization.

Data Type Mismatch: @Key is an int, but @Key_Desc and @Value are varchar. This is fine, but the [Lookup].[Config] table definition used nvarchar for Key_Desc and Value. Using nvarchar for @Key_Desc and @Value parameters in the procedure itself would prevent implicit conversions and data loss if Unicode characters are passed.

No @Advanced update: The @Advanced parameter is only used during INSERT, not during UPDATE. This might be intentional, but worth noting.