/*
* Updates or inserts the system validation value by key name.
*/
CREATE PROCEDURE [dbo].[SetSystemValidationByKey]
    @inNameKey varchar(16),
    @inNewKeyValue varchar(512),
    @inNewExpirationDateUtc datetime
as
set nocount on

declare @theRowCount int

-- It won't matter if multiple calls hit this as all of them should have a valid
-- KeyValue that according to Pyxis should be the same valu.  Thus it will not
-- matter if both of them succeed on this update.
update [Validation] set
    KeyValue = @inNewKeyValue,
    EffectiveDateUtc = getutcdate(),
    ExpirationDateUtc = @inNewExpirationDateUtc
where NameKey = @inNameKey and
    IsSystemValidation = 1

set @theRowCount = @@ROWCOUNT

if( @theRowCount != 1 )
begin
    if( @theRowCount = 0 )
    begin
        -- Only the very first caller should be able to do this.
        insert into [Validation]
        (
            NameKey,
            KeyValue,
            EffectiveDateUtc,
            ExpirationDateUtc,
            IsSystemValidation
        )
        select
            NameKey = @inNameKey,
            KeyValue = @inNewKeyValue,
            EffectiveDateUtc = getutcdate(),
            ExpirationDateUtc = @inNewExpirationDateUtc,
            IsSystemValidation = 1
        where not exists( select top 1 1 from [Validation] where NameKey = @inNameKey and IsSystemValidation = 1 )
    end

    -- We should only ever insert once so make sure we are the only IsSystemValidation row.
    -- This does cleanup should that be necessary.
    set @theRowCount = isnull( ( select top 1 ValidationID from [Validation] where NameKey = @inNameKey and IsSystemValidation = 1 order by ValidationID ), 0 )
    delete from [Validation]
    where NameKey = @inNameKey and
        ValidationID != @theRowCount
end
GO
GRANT EXECUTE ON  [dbo].[SetSystemValidationByKey] TO [MssExec]
GO