1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
| USE [DB_CrossRefV3_test]
GO
/****** Object: StoredProcedure [dbo].[ps_decoupeRef] Script Date: 01/07/2014 09:11:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ps_decoupeRef]
-- Add the parameters for the stored procedure here
@idConcurrent integer,
@idFamille integer,
@refConcurrent nvarchar(50)
AS
BEGIN
declare @tailleRef integer=len(@refConcurrent)
declare @index integer=1
declare @maxParam integer
declare @decoupRef table
(
codeConcurrent integer,
codeFamille integer,
codeParam integer,
ordre integer,
nomParam nvarchar(150),
idValeur integer,
valeurParam nvarchar(10)
)
declare @i integer
select @i=COUNT(1)
from FamilleConcurrent
where idFamille =@idFamille
and codeConcurrent =@idConcurrent
--si le concurrent fait cette famille la
if @i>0
begin
Select @maxParam=max(Ordre)
from CompoRefConcurrent
where CodeConcurrent = @idConcurrent
and idFamille =@idFamille
declare parcour cursor
for
Select idComposante ,Ordre,nomComposante ,[Min],[Max]
from CompoRefConcurrent
where CodeConcurrent = @idConcurrent
and idFamille =@idFamille
order by ordre
declare @codeParam integer
declare @ordre integer
declare @nomParam nvarchar(150)
declare @min integer
declare @max integer
open parcour
fetch parcour into @codeParam,@ordre,@nomParam,@min,@max
while @@FETCH_STATUS = 0
begin
declare @boutRef nvarchar(10)=''
--si c'est le dernier bout de ref on prend le reste
if @ordre =@maxParam
begin
set @boutRef = substring(@refConcurrent,@index,(@tailleRef-@index)+1)
set @index =@index + (@tailleRef-@index)+1
end
else
begin
if @min=@max
begin
set @boutRef = substring(@refConcurrent,@index,@min)
set @index =@index + @min
end
else
begin
declare @count integer=0
if @max-@min=1
begin
select @count=count(ValeurConcurrent)
from ValeurConcurrent
where idComposante =@codeParam
and ValeurConcurrent = substring(@refConcurrent,@index,@max)
if @count>0
begin
set @boutRef = substring(@refConcurrent,@index,@max)
set @index =@index + @max
end
else
begin
set @boutRef = substring(@refConcurrent,@index,@min)
set @index =@index + @min
end
end
else
begin
declare @j integer=@max
while @j>=@min and @count != 1
begin
select @count=count(ValeurConcurrent)
from ValeurConcurrent
where idComposante =@codeParam
and ValeurConcurrent = substring(@refConcurrent,@index,@j)
if @COUNT !=1
set @j=@j-1
end
if @count=1
begin
set @boutRef = substring(@refConcurrent,@index,@j)
set @index =@index + @j
end
end
end
end
declare @idValeur integer=0
select @idValeur=idValeurConcurrent
from ValeurConcurrent
where valeurConcurrent =@boutRef
and idComposante =@codeParam
insert into @decoupRef
values (@idConcurrent,@idFamille ,@codeParam,@ordre,@nomParam,@idValeur ,@boutRef)
fetch parcour into @codeParam,@ordre,@nomParam,@min,@max
end
select * from @decoupRef
close parcour
deallocate parcour
end
end |
