Samuele E. Locatelli
43 lines
1.1 KiB
Transact-SQL
43 lines
1.1 KiB
Transact-SQL
|
|
|
|
-- =============================================
|
|
-- Author: S.E.Locatelli
|
|
-- Create date: 2014.01.15
|
|
-- Description: Recupera CodPackage dato CodItem e CodClient secondo albero decisionale
|
|
-- SOSTITUITA da fun Table-value f_getDataCodPackag in QL_Update
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[f_getCodPackag_OLD]
|
|
(
|
|
@CodItem NVARCHAR(50),
|
|
@CodClient NVARCHAR(50)
|
|
)
|
|
RETURNS NVARCHAR(50)
|
|
AS
|
|
BEGIN
|
|
DECLARE @output NVARCHAR(50)
|
|
DECLARE @trovati INT = 0
|
|
-- setup iniziale: imposto a zero...
|
|
SET @output = '0'
|
|
|
|
SELECT @trovati = COUNT(*) FROM Package2ItemClient WHERE CodClient = @CodClient AND CodItem = @CodItem
|
|
IF(@trovati > 0)
|
|
-- primo tentativo: cerco il package dato SIA cliente che ITEM
|
|
BEGIN
|
|
SELECT TOP 1 @output = CodPackag
|
|
FROM Package2ItemClient WHERE CodClient = @CodClient AND CodItem = @CodItem
|
|
ORDER BY TotQta DESC
|
|
END
|
|
ELSE
|
|
-- secondo tentativo: se non lo trovo cerco per SOLO ITEM
|
|
BEGIN
|
|
SELECT TOP 1 @output = CodPackag
|
|
FROM Package2Item WHERE CodItem = @CodItem
|
|
ORDER BY TotQta DESC
|
|
END
|
|
|
|
RETURN @output
|
|
|
|
END
|
|
|
|
|