Scalar-valued Function Alternative

Question

Scalar-valued Function Alternative

RonMexico

Hall of Fame

Points: 3474
More actions
April 25, 2019 at 2:54 pm

#3535884
We have a scalar-valued function that returns a product based on a series of criteria passed to it by using a bunch of if statements within the function. Not all parameters need to be used and there are many different combinations of criteria as well. I've included some sample code to give a better idea of what I'm talking about.
```
CREATE FUNCTION fnProduct (
@Criteria1 INT
,@Criteria2 INT
,@Criteria3 VARCHAR(5)
,@Criteria4 DATETIME
,@Criteria5 INT
)
RETURNS VARCHAR(25)
AS
BEGIN
DECLARE @product VARCHAR(25)

IF @Criteria1 = 10
SET @product = 'Product1'

IF @Criteria4 <> '4/25/19'
SET @product = 'Product2'

IF @Criteria1 = 22
AND @Criteria2 = 3
AND @Criteria5 <> 50
SET @product = 'Product3'

IF (
@Criteria3 IN (
'abc'
,'xyz'
)
AND @Criteria4 < '1/1/19'
AND @Criteria1 NOT IN (
18
,42
,8
)
)
OR @Criteria2 = 31
SET @product = 'Product4'

RETURN @product
END
```
Obviously, performance can take a hit when using a function like this so I'm trying to think of a better alternative. Ideally I was thinking some kind of lookup table but I'm not sure how that would work since not all criteria needs to be passed and some criteria can have a range of data. My next thought was to rework this as a table-valued function. Are any of those two better options? Is there another solution that I'm not thinking of?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101120 More actions · Answer 1

Off the top, just streamline it as much as you can:

BEGIN
    RETURN (
        SELECT product = CASE 
        WHEN 
    @Criteria3 IN (
'abc'
,'xyz'
)
AND @Criteria4 < '1/1/19'
AND @Criteria1 NOT IN (
18
,42
,8
)
    OR @Criteria2 = 31
        THEN 'Product4'
        WHEN 
            @Criteria1 = 22
    AND @Criteria2 = 3
    AND @Criteria5 <> 50
THEN 'Product3'
        WHEN 
            @Criteria4 <> '4/25/19'
        THEN 'Product2'
        WHEN
            @Criteria1 = 10
        THEN 'Product1'
        ELSE NULL END 
    )
END

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".