SQL Code

CREATE TABLE [dbo].[param] (
[identifier] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[param] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[source] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fromDate] [datetime] NULL ,
[toDate] [datetime] NULL ,
[value] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[updTime] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[param_details] (
[source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[param] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rename] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[priority] [int] NULL ,
[inputYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [param_pk] ON [dbo].[param]([identifier], [param], [toDate], [fromDate], [source]) ON [PRIMARY]
GO
CREATE INDEX [param_val] ON [dbo].[param]([param], [value], [identifier], [toDate]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc get_param
@identifier varchar(100),
@param varchar(100),
@source varchar(30),
@dated datetime
as
if @identifier > ''
begin
select identifier, param, source, fromDate, value
from param
where identifier = @identifier
and param like isnull( @param,param )
and source like isnull( @source,source )
and ( @dated >= fromDate
and @dated < isnull( toDate, '1 jan 3000' )
or @dated is null and toDate is null )
union
select identifier, param, source, fromDate, value
from param
where identifier = @identifier
and param like isnull( @param,param )
and source like isnull( @source,source )
and fromDate is null
end
else
begin
select identifier, param, source, fromDate, value
from param
where param like isnull( @param,param )
and source like isnull( @source,source )
and ( @dated >= fromDate
and @dated < isnull( toDate, '1 jan 3000' )
or @dated is null and toDate is null )
union
select identifier, param, source, fromDate, value
from param
where param like isnull( @param,param )
and source like isnull( @source,source )
and fromDate is null
end
GO
CREATE procedure [dbo].[set_param]
@identifier varchar(100),
@param varchar(100),
@source varchar(30),
@dated datetime,
@value varchar(500),
@stopLoop char(1) = 'n'
as
set nocount on
declare @vf datetime,
@vt datetime,
@rename varchar(100),
@priority int
-- ignore if bad params
if isnull( @param,'' ) = ''
or isnull( @identifier,'' ) = ''
or isnull( @value,'' ) = ''
or isnull( @source,'' ) = ''
or @value like '#N/A%'
or @value ='?'
begin
return
end
select @rename = rename,
@priority = priority
from param_details
where source = @source
and param = @param
and @stopLoop = 'n'
if @rename > ''
begin
if not exists(
-- an existing value from a higher priority source
select 1
from param p1
where p1.identifier = @identifier
and p1.toDate is null
and p1.fromDate >= @dated
and p1.param in (
select param
from param_details
and rename = @rename
and param != @param
and priority < @priority ) )
begin
exec set_param @identifier, @rename, @source, @dated, @value, 'y'
end
end
if @source = 'input'
begin
select @dated = getdate()
end
-- if static value (ie not time dependant)
if @dated is null
begin
-- if value unchanged then return
if exists(
select 1
from param
where identifier = @identifier
and param = @param
and source = @source
and value = @value
and fromDate is null
and toDate is null )
begin
return
end
-- update value to new value if it exists
update param
set value = @value,
updTime = getdate()
where identifier = @identifier
and param = @param
and source = @source
and fromDate is null
-- insert new value if not
if @@rowcount = 0
begin
insert param ( identifier, param, source, fromDate, toDate, value, updTime )
values ( @identifier, @param, @source, null, null, @value, getdate() )
end
end
else
begin
-- if value unchanged then return
if exists(
select 1
from param
where identifier = @identifier
and param = @param
and source = @source
and @dated >= fromDate
and @dated < isnull( toDate, '1 jan 3000' )
and value = @value )
begin
return
end
-- get dates of existing record
select @vf = fromDate,
@vt = toDate
from param
where identifier = @identifier
and param = @param
and source = @source
and @dated >= fromDate
and @dated < isnull( toDate, '1 jan 3000' )
-- if before any current records
if @vt is null
begin
-- get next date
select @vt = min( fromDate )
from param
where identifier = @identifier
and param = @param
and source = @source
and fromDate > @dated
end
-- update fromDate of any existing param for this source on this date
update param
set toDate = @dated,
updTime = getdate()
where identifier = @identifier
and param = @param
and source = @source
and fromDate = @vf
-- add new value
insert param ( identifier, param, source, fromDate, toDate, value, updTime )
select @identifier, @param, @source, @dated, @vt, @value, getdate()
-- if overwriting old data
delete param
where identifier = @identifier
and param = @param
and source = @source
and fromDate = @dated
and value != @value
end
GO