Functions (Database)

---

« Contents / Database

---

The metadatabase database uses two very simple scalar functions to assist other stored procedures. These are as follows:

---

GetPropertyValueInternal

Schema: procfwk

Input: @PropertyName VARCHAR(128)

Output: @PropertyValue NVARCHAR(MAX)

Role: the role of this function is to wrap up and return property values from the database properties table when required as part of other internal stored procedure logic. For example; when a stored procedure IF condition needs a property value as part of its valuation logic.

As the name suggests this scalar function is only used internally by other database objects. When the orchestrator requires a property value the stored procedure [procfwk].[GetPropertyValue] is used.

Example Use:

IF ([procfwk].[GetPropertyValueInternal]('FailureHandling')) = 'DependencyChain'
BEGIN
    --output
END

---

CheckForValidURL

Schema: procfwkHelpers

Input: @Url NVARCHAR(MAX)

Output: RETURN 0 or 1; Depending on input string conditions.

Role: as a helper function this is in turn used as part of the helper stored procedure [procfwkHelpers].[AddServicePrincipalUrls]. When adding secret URL values that will later be retrieved from Azure Key Vault as part of the SPN handling behaviour the function performs soft validation at entry time to assist in ensuring the metadata is valid. The validation and string parsing is specific to a Key Vault Secret URL and isn’t a generic validator for any URL.

Example Use:

IF ([procfwkHelpers].[CheckForValidURL](@PrincipalIdUrl)) = 0
BEGIN
	SET @ErrorDetails = 'PrincipalIdUrl value is not in the expected format. . Please confirm the URL follows the structure https://{YourKeyVaultName}.vault.azure.net/secrets/{YourSecretName} and does not include the secret version guid.'
	PRINT @ErrorDetails;
END

IF ([procfwkHelpers].[CheckForValidURL](@PrincipalSecretUrl)) = 0
BEGIN
	SET @ErrorDetails = 'PrincipalSecretUrl value is not in the expected format. Please confirm the URL follows the structure https://{YourKeyVaultName}.vault.azure.net/secrets/{YourSecretName} and does not include the secret version guid.'
	PRINT @ErrorDetails;		
END

---