Sulprobil
Search…
SQL Code
1
CREATE TABLE [dbo].[param] (
2
[identifier] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
3
[param] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
4
[source] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
5
[fromDate] [datetime] NULL ,
6
[toDate] [datetime] NULL ,
7
[value] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
8
[updTime] [datetime] NULL
9
) ON [PRIMARY]
10
11
GO
12
13
CREATE TABLE [dbo].[param_details] (
14
[source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
15
[param] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
16
[idYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
17
[rename] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
18
[priority] [int] NULL ,
19
[inputYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
20
) ON [PRIMARY]
21
22
GO
23
24
CREATE UNIQUE INDEX [param_pk] ON [dbo].[param]([identifier], [param], [toDate], [fromDate], [source]) ON [PRIMARY]
25
26
GO
27
28
CREATE INDEX [param_val] ON [dbo].[param]([param], [value], [identifier], [toDate]) ON [PRIMARY]
29
30
GO
31
32
SET QUOTED_IDENTIFIER ON
33
34
GO
35
36
SET ANSI_NULLS ON
37
38
GO
39
40
CREATE proc get_param
41
@identifier varchar(100),
42
@param varchar(100),
43
@source varchar(30),
44
@dated datetime
45
as
46
if @identifier > ''
47
begin
48
select identifier, param, source, fromDate, value
49
from param
50
where identifier = @identifier
51
and param like isnull( @param,param )
52
and source like isnull( @source,source )
53
and ( @dated >= fromDate
54
and @dated < isnull( toDate, '1 jan 3000' )
55
or @dated is null and toDate is null )
56
union
57
select identifier, param, source, fromDate, value
58
from param
59
where identifier = @identifier
60
and param like isnull( @param,param )
61
and source like isnull( @source,source )
62
and fromDate is null
63
end
64
else
65
begin
66
select identifier, param, source, fromDate, value
67
from param
68
where param like isnull( @param,param )
69
and source like isnull( @source,source )
70
and ( @dated >= fromDate
71
and @dated < isnull( toDate, '1 jan 3000' )
72
or @dated is null and toDate is null )
73
union
74
select identifier, param, source, fromDate, value
75
from param
76
where param like isnull( @param,param )
77
and source like isnull( @source,source )
78
and fromDate is null
79
end
80
81
GO
82
83
CREATE procedure [dbo].[set_param]
84
@identifier varchar(100),
85
@param varchar(100),
86
@source varchar(30),
87
@dated datetime,
88
@value varchar(500),
89
@stopLoop char(1) = 'n'
90
as
91
set nocount on
92
93
declare @vf datetime,
94
@vt datetime,
95
@rename varchar(100),
96
@priority int
97
98
-- ignore if bad params
99
if isnull( @param,'' ) = ''
100
or isnull( @identifier,'' ) = ''
101
or isnull( @value,'' ) = ''
102
or isnull( @source,'' ) = ''
103
or @value like '#N/A%'
104
or @value ='?'
105
begin
106
return
107
end
108
109
select @rename = rename,
110
@priority = priority
111
from param_details
112
where source = @source
113
and param = @param
114
and @stopLoop = 'n'
115
116
if @rename > ''
117
begin
118
if not exists(
119
-- an existing value from a higher priority source
120
select 1
121
from param p1
122
where p1.identifier = @identifier
123
and p1.toDate is null
124
and p1.fromDate >= @dated
125
and p1.param in (
126
select param
127
from param_details
129
and rename = @rename
130
and param != @param
131
and priority < @priority ) )
132
begin
133
exec set_param @identifier, @rename, @source, @dated, @value, 'y'
134
end
135
end
136
137
if @source = 'input'
138
begin
139
select @dated = getdate()
140
end
141
142
-- if static value (ie not time dependant)
143
if @dated is null
144
begin
145
146
-- if value unchanged then return
147
if exists(
148
select 1
149
from param
150
where identifier = @identifier
151
and param = @param
152
and source = @source
153
and value = @value
154
and fromDate is null
155
and toDate is null )
156
begin
157
return
158
end
159
160
-- update value to new value if it exists
161
update param
162
set value = @value,
163
updTime = getdate()
164
where identifier = @identifier
165
and param = @param
166
and source = @source
167
and fromDate is null
168
169
-- insert new value if not
170
if @@rowcount = 0
171
begin
172
insert param ( identifier, param, source, fromDate, toDate, value, updTime )
173
values ( @identifier, @param, @source, null, null, @value, getdate() )
174
end
175
end
176
else
177
begin
178
179
-- if value unchanged then return
180
if exists(
181
select 1
182
from param
183
where identifier = @identifier
184
and param = @param
185
and source = @source
186
and @dated >= fromDate
187
and @dated < isnull( toDate, '1 jan 3000' )
188
and value = @value )
189
begin
190
return
191
end
192
193
-- get dates of existing record
194
select @vf = fromDate,
195
@vt = toDate
196
from param
197
where identifier = @identifier
198
and param = @param
199
and source = @source
200
and @dated >= fromDate
201
and @dated < isnull( toDate, '1 jan 3000' )
202
203
-- if before any current records
204
if @vt is null
205
begin
206
-- get next date
207
select @vt = min( fromDate )
208
from param
209
where identifier = @identifier
210
and param = @param
211
and source = @source
212
and fromDate > @dated
213
end
214
215
-- update fromDate of any existing param for this source on this date
216
update param
217
set toDate = @dated,
218
updTime = getdate()
219
where identifier = @identifier
220
and param = @param
221
and source = @source
222
and fromDate = @vf
223
224
-- add new value
225
insert param ( identifier, param, source, fromDate, toDate, value, updTime )
226
select @identifier, @param, @source, @dated, @vt, @value, getdate()
227
228
-- if overwriting old data
229
delete param
230
where identifier = @identifier
231
and param = @param
232
and source = @source
233
and fromDate = @dated
234
and value != @value
235
end
236
237
GO
Copied!
Last modified 1yr ago
Copy link