giancarlo
116 lines
3.7 KiB
SQL
116 lines
3.7 KiB
SQL
/*
|
|
UPDATE SQL Schema 3.2
|
|
*/
|
|
SET NUMERIC_ROUNDABORT OFF
|
|
GO
|
|
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
|
|
GO
|
|
SET XACT_ABORT ON
|
|
GO
|
|
SET TRANSACTION ISOLATION LEVEL Serializable
|
|
GO
|
|
BEGIN TRANSACTION
|
|
GO
|
|
IF @@ERROR <> 0 SET NOEXEC ON
|
|
GO
|
|
PRINT N'Altering [dbo].[v_Misure]'
|
|
GO
|
|
|
|
ALTER VIEW [dbo].[v_Misure]
|
|
AS
|
|
SELECT mi.IdxDossier,
|
|
mi.CodScheda+'.'+CAST(mi.Vers AS NVARCHAR(50))+'.'+CAST(mi.NumScheda AS NVARCHAR(50)) AS CodSchedaVersNum,
|
|
mi.CodScheda,
|
|
mi.Vers,
|
|
mi.NumScheda,
|
|
mi.CodMisura,
|
|
mi.Fase,
|
|
mi.Rev,
|
|
mi.Operatore,
|
|
mi.DataOra,
|
|
mi.ValorePar,
|
|
mi.ValoreMis,
|
|
mi.OkValMis,
|
|
mi.CodDeroga,
|
|
ds.NomeMisura,
|
|
ds.TipoValMisura,
|
|
ds.NomePar,
|
|
ISNULL(vv.MinPar,0) AS MinPar,
|
|
ISNULL(vv.MaxPar,999999999) AS MaxPar,
|
|
ISNULL(vv.LIVal,0) AS LIVal,
|
|
ISNULL(vv.LSVal,999999999) AS LSVal,
|
|
mi.CodVisib,
|
|
mi.Ordinale
|
|
FROM dbo.Misure AS mi
|
|
INNER JOIN dbo.DettScheda AS ds ON mi.CodScheda = ds.CodScheda
|
|
AND mi.Vers = ds.Vers
|
|
AND mi.CodMisura = ds.CodMisura
|
|
LEFT OUTER JOIN dbo.v_ValidVal AS vv ON ds.CodScheda = vv.CodScheda
|
|
AND ds.Vers = vv.Vers
|
|
AND ds.CodMisura = vv.CodMisura
|
|
AND mi.ValorePar >= ISNULL(vv.MinPar, 0)
|
|
WHERE(ISNULL(vv.MaxPar, 999999999) > mi.ValorePar);
|
|
|
|
GO
|
|
IF @@ERROR <> 0 SET NOEXEC ON
|
|
GO
|
|
PRINT N'Altering [dbo].[stp_Mis_getDataFilt]'
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2015.06.17
|
|
-- Description: elenco records filtrati
|
|
-- =============================================
|
|
ALTER PROCEDURE [dbo].[stp_Mis_getDataFilt]
|
|
(
|
|
@IdxDossier INT
|
|
,@CodSchedaVers NVARCHAR(100) -- formato CodScheda.Vers.Num --> da splittare
|
|
,@Fase NVARCHAR(50)
|
|
,@Rev INT
|
|
)
|
|
AS
|
|
|
|
DECLARE @CodScheda NVARCHAR(50) = '',
|
|
@Vers INT = 0,
|
|
@NumScheda INT = 0
|
|
|
|
-- solo se HO dei dati da splittare..
|
|
IF @CodSchedaVers <> ''
|
|
BEGIN
|
|
-- splitto e recupero dati...
|
|
SELECT @CodScheda = SUBSTRING ( @CodSchedaVers, 0, CHARINDEX ( '.', @CodSchedaVers ) )
|
|
SELECT @CodSchedaVers = REPLACE ( @CodSchedaVers, @CodScheda+'.', '' )
|
|
SELECT @Vers = SUBSTRING ( @CodSchedaVers, 0, CHARINDEX ( '.', @CodSchedaVers ) )
|
|
SELECT @NumScheda = REPLACE ( @CodSchedaVers, CAST(@Vers AS NVARCHAR(10))+'.', '' )
|
|
END
|
|
|
|
SELECT *
|
|
FROM v_Misure
|
|
WHERE IdxDossier = CASE WHEN @IdxDossier <> 0 THEN @IdxDossier ELSE IdxDossier END
|
|
AND CodScheda = CASE WHEN @CodScheda <> '' THEN @CodScheda ELSE CodScheda END
|
|
AND Vers = CASE WHEN @Vers <> 0 THEN @Vers ELSE Vers END
|
|
AND NumScheda = CASE WHEN @NumScheda <> 0 THEN @NumScheda ELSE NumScheda END
|
|
AND Fase = CASE WHEN @Fase <> '' THEN @Fase ELSE Fase END
|
|
AND Rev = CASE WHEN @Rev >= 0 THEN @Rev ELSE Rev END
|
|
ORDER BY Ordinale, CodScheda, Vers, NumScheda, CodMisura, NomeMisura
|
|
|
|
RETURN
|
|
|
|
GO
|
|
IF @@ERROR <> 0 SET NOEXEC ON
|
|
GO
|
|
COMMIT TRANSACTION
|
|
GO
|
|
IF @@ERROR <> 0 SET NOEXEC ON
|
|
GO
|
|
DECLARE @Success AS BIT
|
|
SET @Success = 1
|
|
SET NOEXEC OFF
|
|
IF (@Success = 1) PRINT 'The database update succeeded'
|
|
ELSE BEGIN
|
|
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
|
|
PRINT 'The database update failed'
|
|
END
|
|
GO
|