Comment convertir une instruction CASE en une fonction définie par l'utilisateur en T-SQL ?

Question

J'utilise SQL Server 2014 et j'ai l'instruction CASE suivante dans une requête T-SQL.

(CASE          
     WHEN c.[Market FINAL] = 'Overbooking' AND c.[Booking type] = 'GRP' THEN 'Overbooking'
     WHEN c.[TaProfileID] = 853 THEN 'Poland (TUI only)'
     WHEN c.[Source of Business] = 'TO' AND c.[CountryName] = 'Netherlands' THEN 'Netherlands'
     WHEN c.[Source of Business] = 'DMC' AND c.[Booking Origin (1)] = 'Netherlands' THEN 'Netherlands'
     WHEN c.[Booking type] = 'GRP' THEN 'G&I'   
  ELSE c.[Market FINAL]
 END) AS 'Market',

Je voudrais la supprimer de cette requête et l'exécuter comme une fonction définie par l'utilisateur, mais j'ai du mal à créer la fonction. En outre, comment puis-je l'utiliser dans ma requête une fois qu'elle est exécutée en tant que fonction ?

Answer 1

    CREATE FUNCTION \[dbo\].\[\_fnMyCase\] ( @Filter1 AS VARCHAR(20)
                                  , @Filter2 AS VARCHAR(20))      
    RETURNS VARCHAR(20)
    AS
    BEGIN

        DECLARE @MyReturn AS VARCHAR(20) = ''

        SELECT @MyReturn = 
                (CASE          
                     WHEN c.\[Market FINAL\] = 'Overbooking' 
                        AND c.\[Booking type\] = 'GRP' 
                        THEN 'Overbooking'
                     WHEN c.\[TaProfileID\] = 853 
                        THEN 'Poland (TUI only)'
                     WHEN c.\[Source of Business\] = 'TO' 
                        AND c.\[CountryName\] = 'Netherlands' 
                        THEN 'Netherlands'
                     WHEN c.\[Source of Business\] = 'DMC' 
                        AND c.\[Booking Origin (1)\] = 'Netherlands' 
                        THEN 'Netherlands'
                     WHEN c.\[Booking type\] = 'GRP' 
                        THEN 'G&I'   
                     ELSE c.\[Market FINAL\]
                 END)   
        FROM c
        WHERE Filter1 = @Filter1 AND Filter2 = @Filter2   

        RETURN @MyReturn        

    END

Answer 2

Cette logique ressemble à un défaut de conception. Cependant, puisque toutes les colonnes sont dans la même table, je suggérerais une colonne calculée :

CREATE TABLE SomeTable
(
    [Market Final] varchar(20),
    [Booking type] varchar(10),
    TaProfileId int,
    [Source of Business] varchar(10),
    [CountryName] varchar(20),
    [Booking Origin (1)] varchar(10),
--Other fields
    Market AS
        CASE          
            WHEN [Market FINAL] = 'Overbooking' AND [Booking type] = 'GRP' THEN 'Overbooking'
            WHEN [TaProfileID] = 853 THEN 'Poland (TUI only)'
            WHEN [Source of Business] = 'TO' AND [CountryName] = 'Netherlands' THEN 'Netherlands'
            WHEN [Source of Business] = 'DMC' AND [Booking Origin (1)] = 'Netherlands' THEN 'Netherlands'
            WHEN [Booking type] = 'GRP' THEN 'G&I'   
            ELSE [Market FINAL]
        END
)

Ensuite, au lieu de votre expression complexe, vous pouvez utiliser :

SELECT c.Market, ...
FROM SomeTable c

Answer 3

Vous devez créer une fonction comme celle qui suit.

CREATE FUNCTION [dbo].[fnMyCase] (
    @MarketFINAL VARCHAR(50)
    ,@Bookingtype VARCHAR(50)
    ,@TaProfileID INT
    ,@SourceofBusiness VARCHAR(50)
    ,@CountryName VARCHAR(50)
    ,@BookingOrigin VARCHAR(50)
    )
RETURNS VARCHAR(20)
AS
BEGIN
    DECLARE @MyReturn AS VARCHAR(50) = ''

    SELECT @MyReturn = (
            CASE 
                WHEN @MarketFINAL = 'Overbooking'
                    AND @Bookingtype = 'GRP'
                    THEN 'Overbooking'
                WHEN @TaProfileID = 853
                    THEN 'Poland (TUI only)'
                WHEN @SourceofBusiness = 'TO'
                    AND @CountryName = 'Netherlands'
                    THEN 'Netherlands'
                WHEN @SourceofBusiness = 'DMC'
                    AND @BookingOrigin = 'Netherlands'
                    THEN 'Netherlands'
                WHEN @Bookingtype = 'GRP'
                    THEN 'G&I'
                ELSE @MarketFINAL
                END
            )

    RETURN @MyReturn
END

Appelez la fonction comme ceci,

SELECT *
    ,[dbo].[fnMyCase](c.[Market FINAL], c.[Booking type], c.[TaProfileID], c.[Source of Business], c.[CountryName], c.[Booking Origin (1)]) AS Market
FROM c