Purpose
Tracks a set of key/value pairs for each Workspace that provide working parameters for the framework.
The most common use case is keeping track of counters for Instance records that require them.
Definition
CREATE TABLE [dbo].[Parameters](
[Workspace] [VARCHAR](50) NOT NULL,
[ParameterName] [VARCHAR](50) NOT NULL,
[ParameterValue] [VARCHAR](50) NOT NULL,
CONSTRAINT [PK_Parameters] PRIMARY KEY CLUSTERED
(
[Workspace] ASC,
[ParameterName] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]Column Definition
Workspace names the Workspace that owns this parameter key/value pair.
ParameterName provides the key for the Parameter.
ParameterValue provides the value for the Parameter.
Key Functionality
The GI_IncrementParameter TSQL Stored Procedure is used to increment parameter values by one and return the new value. It automatically detects the Workspace based on the specified UserGUID. If the named Parameter does not already exist, it is automatically created and set to one.
CREATE PROCEDURE [dbo].[GI_IncrementParameter]
(
@UserGUID AS UNIQUEIDENTIFIER,
@Name AS VARCHAR(50),
@Value AS BIGINT OUTPUT
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN
BEGIN TRY
DECLARE @ReturnStatus AS INTEGER
DECLARE @Rows AS INTEGER
DECLARE @Workspace as varchar(300)
-- Get the workspace name
SELECT @Workspace = W.InstanceName
FROM dbo.vw_ZWO_Workspace W WITH (NOLOCK)
INNER JOIN dbo.Instances U WITH (NOLOCK) ON W.InstanceGUID = U.ParentGUID
WHERE U.InstanceGUID = @UserGUID
-- Does this parameter exist?
SELECT @Rows = COUNT(ParameterName)
FROM dbo.Parameters WITH (NOLOCK)
WHERE Workspace = @Workspace AND ParameterName = @Name
-- IF The parameter does not already exist THEN
IF @Rows = 0
BEGIN
-- Insert the zero value...
INSERT INTO dbo.Parameters (Workspace, ParameterName, ParameterValue)
VALUES (@Workspace, @Name, '0')
END
-- Increment the value of the parameter
BEGIN TRY
BEGIN TRANSACTION
-- Read the current value of the parameter...
SELECT @Value = CONVERT(INTEGER, ParameterValue)
FROM dbo.Parameters
WHERE Workspace = @Workspace AND ParameterName = @Name
-- Increment the parameter...
SET @Value = @Value + 1
-- Save the parameter...
UPDATE dbo.Parameters
SET ParameterValue = CONVERT(VARCHAR(1000), @Value)
WHERE Workspace = @Workspace AND ParameterName = @Name
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Log and raise the error...
ROLLBACK TRANSACTION
EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_IncrementParameter - Unknown error incrementing parameter', 1, NULL
RAISERROR ('DB.GI_IncrementParameter - Unknown error incrementing parameter', 16, 1)
RETURN 1
END CATCH
-- The parameter value was set...
RETURN 0
END TRY
BEGIN CATCH
-- Log and raise the error...
IF @@TRANCOUNT > 0
BEGIN
DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
ROLLBACK TRANSACTION
EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_IncrementParameter - Unknown error', 1, @ErrorMessage
RAISERROR ('DB.GI_IncrementParameter - Unknown error', 16, 1)
END
RETURN 1
END CATCH
END