Replacement for scalar function by inline function

Question

I have a proc which currently uses scalar function, in select statement twice which is described below.Is it better performance wise to replace it with inline function when we deal with millions of records.If so what should be it

CREATE FUNCTION getcategory
(
    @shopping_store CHAR(4),
    @cart_type      CHAR(2),
    @category_type  INT,
    @index_cat_type INT
)
RETURNS INT
BEGIN
    IF @shopping_store IN ('1111','1222','3222') AND @cart_type in ('120')
        RETURN -@category_type
    ELSE IF @shopping_store IN ('4333','54322') AND @cart_type IN ('120')
        RETURN @index_cat_type
    ELSE
    BEGIN
        IF @shopping_store IN ('32214','5432','5654')
            RETURN @category_type
        ELSE
            RETURN -@index_cat_type
    END

    RETURN @category_type
END

Answer 1

All those IF ELSEs can be converted into a single case expression. This then lets you convert this scalar function to an inline table valued function. The performance benefit of this should be fairly substantial as you state you have millions of rows. I also took the liberty of changing @cart_type to a char(4) since as GarethD pointed out it can't even contain '120'. I also used explicit multiplication when wanting a negative number because it is too easy to miss a - at the beginning, it is very clear when you multiply by negative 1.

CREATE FUNCTION getcategory
(
    @shopping_store CHAR(4),
    @cart_type      CHAR(4),
    @category_type  INT,
    @index_cat_type INT
)
RETURNS TABLE as RETURN
    select case 
        when @shopping_store IN ('1111','1222','3222') AND @cart_type in ('120') 
            then -1 * @category_type
        when @shopping_store IN ('4333','54322') AND @cart_type IN ('120')
            then @index_cat_type
        when @shopping_store IN ('32214','5432','5654')
            then @category_type
        ELSE
            -1 * @index_cat_type
    END as CategoryType