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 |
Partager