Samuele E. Locatelli
52 lines
2.1 KiB
SQL
52 lines
2.1 KiB
SQL
-- =============================================
|
|
-- Author: G.A.R.
|
|
-- Mad. date: 2014-06-06
|
|
-- Description: Ritorna il package e i dati associati dove presenti
|
|
-- sostituisce la relativa funzione UDF f_getDataCodPackag
|
|
-- =============================================
|
|
CREATE PROCEDURE [stp_getDataCodPackag]
|
|
|
|
@CodItem NVARCHAR(50),
|
|
@CodClient NVARCHAR(50),
|
|
-- output variable
|
|
@CodPackag NVARCHAR(50) OUTPUT,
|
|
@TotQta DECIMAL(18, 3) OUTPUT,
|
|
@Weight DECIMAL(18, 6) OUTPUT,
|
|
@Tare DECIMAL(18, 6) OUTPUT,
|
|
@NumImb DECIMAL(9, 6) OUTPUT,
|
|
@Durata DECIMAL(18, 6) OUTPUT,
|
|
@LivPackag NVARCHAR(50) OUTPUT,
|
|
@LivPackagDet NVARCHAR(50) OUTPUT
|
|
AS
|
|
BEGIN
|
|
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
IF EXISTS ( SELECT * FROM dbo.Package2ItemClient WHERE CodClient = @CodClient AND CodItem = @CodItem)
|
|
-- primo tentativo: cerco il package dato SIA cliente che ITEM
|
|
BEGIN
|
|
SELECT TOP 1 @CodPackag = CodPackag, @TotQta = TotQta, @Weight = Weight, @Tare = Tare, @NumImb = NumImb, @Durata = Durata
|
|
,@LivPackag = 'Item-Client-Package' , @LivPackagDet = @CodItem + '-' + @CodClient + '-' + CodPackag
|
|
FROM dbo.Package2ItemClient
|
|
WHERE CodClient = @CodClient AND CodItem = @CodItem
|
|
ORDER BY TotQta DESC
|
|
END
|
|
ELSE IF EXISTS ( SELECT * FROM Package2Item WHERE CodItem = @CodItem )
|
|
-- secondo tentativo: se non lo trovo cerco per SOLO ITEM
|
|
BEGIN
|
|
SELECT TOP 1 @CodPackag = CodPackag, @TotQta = TotQta, @Weight = Weight, @Tare = Tare, @NumImb = NumImb, @Durata = Durata
|
|
,@LivPackag = 'Item-Package', @LivPackagDet = @CodItem + '-' + CodPackag
|
|
FROM dbo.Package2Item WHERE CodItem = @CodItem
|
|
ORDER BY TotQta DESC
|
|
END
|
|
ELSE
|
|
-- secondo tentativo: se non lo trovo cerco per SOLO ITEM
|
|
BEGIN
|
|
SELECT TOP 1 @CodPackag = CodPackag, @TotQta = TotQta, @Weight = Weight, @Tare = Tare, @NumImb = NumImb, @Durata = Durata
|
|
,@LivPackag = 'Package', @LivPackagDet = CodPackag
|
|
FROM dbo.Package2 WHERE CodPackag = 0
|
|
END
|
|
END
|
|
|
|
RETURN |