CODEWITHSUNDEEP
sqlt-sqluser-defined-functions
Nikolas Skurupatis
Nikolas Skurupatis

Reputation: 43

Pass Table as parameter to function

I have a temp table which contains data needed in selection, but I can't join in to main select statement because it contains too many rows, and grouping is not good enough. So I decided to use values directly from my temp table in the selection, and it works fine. But since I need to add selection from temp table 50 times as sub queries in that main selection, I was considering to move it to function, as is nicer to call a function 50 times than sub queries. My question is how to pass values from that table to function? Function will be also supplied with other parameters needed for extraction exact value from that table. I know I can't pass temp table as a parameter, but what about table variable? I don't care if I use temp table or a table variable.. Firstly I wrote a function containing the select statement same as for temp table, and it works but too slow. So if I could pass table results to a function, it will speed up the process..

My function now look like this:

ALTER FUNCTION [document].[GetPersonPremium] 
(
    -- Add the parameters for the function here
    @DocumentId bigint,
    @PersonId bigint,
    @PeriodId int,
    @PersonRole nvarchar(20)
)
RETURNS DECIMAL (18,2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @premiumSum decimal (18,2)


    -- Add the T-SQL statements to compute the return value here
    set @premiumSum = 
        (select top 1 pt.Premium from document.Document d
        inner join document.Person p on p.DocumentCalculationLayerID = dcl.DocumentCalculationLayerID
        inner join document.PersonTasks pt on pt.PersonId = p.PersonId
        inner join document.PersonCalculationHelper pch on pch.PersonTaskId = pt.PersonTaskId
        inner join document.PersonTaskCalculationHelper ptch on ptch.PersonId = p.PersonId
        inner join document.PersonMarkTypes pmt on pmt.ConcernMarkTypeID = ptch.ConcernMarkTypeId
        where dcl.DocumentID = @DocumentId and p.PersonId = @PersonId and pch.PeriodId = @PeriodId and pmt.Name = @PersonRole)

    -- Return the result of the function
    RETURN @premiumSum

END

And I would like to use it from stored procedure like this:

...
    Engineer = Coalesce(document.GetPersonPremium(@DocumentId, p.PersonID, 65, 'Intern'), 0.00),
...

Any suggestion?

Upvotes: 1

Views: 3288

Answers (1)

Nikolas Skurupatis
Nikolas Skurupatis

Reputation: 43

The answer is for those who come to this page with same or similar problem. I created a table type:

CREATE TYPE PremiumTableType AS TABLE
(
    PersonId bigint,
    PeriodId int,
    PersonRole nvarchar(20),
)

Included it in function:

ALTER FUNCTION [document].[GetPersonPremium] 
(
    -- Add the parameters for the function here
    @DocumentId bigint,
    @PersonId bigint,
    @PeriodId int,
    @PersonRole nvarchar(20),
    @PremiumTableType PremiumTableType readonly
)
RETURNS DECIMAL (18,2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @premiumSum decimal (18,2)


    -- Add the T-SQL statements to compute the return value here
    set @premiumSum = (select p.Premium from @PremiumType p where p.PersonId = @PersonId and p.PeriodId = @PeriodId and p.PersonRole = @PersonRole)

    -- Return the result of the function
    RETURN @premiumSum

END

In SP declared table type variable

Declare @PremiumTableType PremiumTableType

Inserted data from my temp table

Insert into @PremiumTableType (PersonID, PeriodId, ConcernRole, PersonPremium)
Select p.PersonID, ...

And called function from SP like

document.GetPersonPremium(@DocumentID, p.PersonID, pt.PeriodID, 'Intern', @PremiumTableType)

Upvotes: 3

Related Questions