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
| ALTER PROCEDURE [dbo].[LBSA_Calcul_mesures]-- @id_lay int = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @id_lay int;
DECLARE @mesure nVarchar(50);
DECLARE @nom_attri nvarchar(20);
DECLARE @cible float;
DECLARE @tol_min float;
DECLARE @tol_max float;
DECLARE @resultat float;
DECLARE @conforme nVarchar(10);
SET @id_lay = 1;
DECLARE nom_mesure CURSOR
FOR SELECT nom_attri,cible, tol_min, tol_max From LBSA_CCTRL_attrib WHERE id_lay = @id_lay;
-- Insert statements for procedure here
SET @mesure =( SELECT mesure_axe_q From LBSA_CCTRL_Txt);
IF @mesure IS NULL
Begin
SET @mesure =( SELECT mesure_val_test From LBSA_CCTRL_Csv);
END
DECLARE res_mes CURSOR FOR SELECT @mesure;
OPEN nom_mesure;
FETCH NEXT FROM nom_mesure INTO
@nom_attri,
@cible,
@tol_min,
@tol_max
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @nom_attri;
OPEN res_mes;
FETCH NEXT FROM res_mes INTO
@resultat
WHILE @@FETCH_STATUS = 0
Begin
IF @resultat = @cible
begin
Set @conforme = 'conforme';
end
IF @resultat < @tol_min OR @resultat > @tol_max
Begin
Set @conforme = 'non-conforme';
end
ELSE
begin
Set @conforme = 'non-conforme';
end
END
END;
INSERT INTO LBSA_CCTRL_resultat (id_lay,nom_attri,cible,tol_min,tol_max,Conforme)VALUES(@id_lay,@nom_attri,@cible,@tol_min,@tol_max,@conforme)
CLOSE res_mes;
DEALLOCATE res_mes;
CLOSE nom_mesure;
DEALLOCATE nom_mesure;
END |
Partager