Is it Possible to Create an Inline Table-valued Function out of this Scalar Function?

Question

I'm pretty sure the answer is No, but I want to double check. If there is a way, you don't actually have to code it, a suggestion on a method might help. I've tried 1 big Select with sub query's, etc. but I can't figure out how to get it to pick the correct record.

The table has to join back itself to get inverse relationships. The code below is just a test sample.

Thank you

create  function    [dbo].[fnUOMFactor_Inline]  (
        @ItemID     nvarchar(20)
,       @FromUnit   nvarchar(10)
,       @ToUnit     nvarchar(10)
    )

returns numeric(28,12)
as
 begin

declare @Factor     numeric(28,12)


if      @FromUnit = @ToUnit
set     @Factor = 1

--      Simple 1-step
if      @Factor is null
select  @Factor = factor
from    dbo.UnitConvertTest 
WHere   itemid = @ItemID
and     fromunit = @FromUnit
and     tounit = @ToUnit


--      Inverted 1-step
if      @Factor is null
select  @Factor = 1/factor 
from    dbo.UnitConvertTest 
Where   itemid = @ItemID
and     fromunit = @ToUnit
and     tounit = @FromUnit


if      @Factor is null
select  @Factor = uc1.factor * uc2.factor
from    dbo.UnitConvertTest uc1 
join    dbo.UnitConvertTest uc2  on uc1.itemid = uc2.itemid
                            and uc1.tounit = uc2.fromunit

where   uc1.itemid = @ItemID
and     uc1.fromunit = @FromUnit
and     uc2.tounit = @ToUnit
and     uc1.factor <> 0
and     uc2.factor <> 0

--      Inverted 2-step
if      @Factor is null
select  @Factor = 1 / (uc1.factor * uc2.factor)
from    dbo.UnitConvertTest uc1 
join    dbo.UnitConvertTest uc2     on  uc1.itemid = uc2.itemid
                                and uc1.tounit = uc2.fromunit
Where   uc1.itemid = @ItemID
and     uc1.fromunit = @ToUnit
and     uc2.tounit = @FromUnit

--      Complex 2-step (same fromunit)
if      @Factor is null
select  @Factor = uc1.factor / uc2.factor
from    dbo.UnitConvertTest uc1 
join    dbo.UnitConvertTest uc2 on  uc1.itemid = uc2.itemid
                            and uc1.fromunit = uc2.fromunit
Where   uc1.itemid = @ItemID
and     uc1.tounit = @ToUnit
and     uc2.tounit = @FromUnit


--      Complex 2-step (same tounit)
if      @Factor is null
select  @Factor = uc1.factor / uc2.factor
from    dbo.UnitConvertTest uc1 
join    dbo.UnitConvertTest uc2  on uc1.itemid = uc2.itemid
                            and uc1.tounit = uc2.tounit
Where   uc1.itemid = @ItemID
and     uc1.fromunit = @FromUnit
and     uc2.fromunit = @ToUnit

--      Default
if      @Factor is null
set     @Factor = 1

return  @Factor
end

This is a table with a few records for testing.

CREATE TABLE [dbo].[UnitConvertTest](
[FROMUNIT] [nvarchar](10) NOT NULL,
[TOUNIT]  [nvarchar](10) NOT NULL,
[FACTOR] [numeric](28,12) NOT NULL,
[ITEMID] [nvarchar](20) NOT NULL,
) ON [PRIMARY]

insert into dbo.UnitConvertTest (FROMUNIT, TOUNIT, FACTOR, ITEMID)

 Values ('CT','PT','40.00','TEST')
 ,      ('RM','CT','10.00','TEST')
 ,      ('RM','PT','400.00','TEST')


 Select [dbo].[fnUOMFactor_Inline] ('Test','PT','RM')

usr · Accepted Answer · 2016-05-25 20:44:37Z

1

Any loop-free scalar function can be turned into an inline TVP through mechanical transformations. The transformations might result in a big query, though.

Your code seems to be a chain of fallback computations. You can write it like this:

select FinalResult = coalesce(x.factor, f1.fallback, f2.fallback, f3.fallback, ...)
from (values (convert(decimal(28, 12, null))) x(factor)
cross apply (select fallback = {computeFallback1}) f1
cross apply (select fallback = {computeFallback2}) f2
cross apply (select fallback = {computeFallback3}) f3
...

Essentially, this models a series of scalar computations through a one-row table that we continuously add columns to...

This might result in a query with performance issues. It will likely force loop joins.

answered May 25, 2016 at 20:44

usr

172k38 gold badges251 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Squires Over a year ago

Genius, thank you ! So far, I was able to re-write the function into an inline version using this, and my test example works perfectly. I ran it through some larger data sets and it seemed to work and be a performance improvement. I then ran it through an even bigger data set, and I got the error below. I'm not sure how that's possible based on looking at the structure of the function. Any idea?error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

David Squires Over a year ago

I guess I see where the issue could be.. In your example, I have sub query's in {computefallback}. Somewhere, i'm meeting the criteria to result in 2 rows. I guess the old function acts like an "Update" in those scenarios, and just picks the 1st value. Maybe I can add a MAX() to pick a factor like the existing logic is doing.

usr Over a year ago

The old code had a bug then. A non-deterministict value was chosen... Right, you can use MAX or TOP 1 ORDER BY ....

David Squires Over a year ago

Awesome, thank you again ! Let me know if i didn't give you all the proper credit / rep. That was a huge help

Collectives™ on Stack Overflow

Is it Possible to Create an Inline Table-valued Function out of this Scalar Function?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related