April 25, 2019 at 2:54 pm
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?
April 25, 2019 at 3:24 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply