How to pass user-defined table type to inline function

Question

I have some complex function that I want to use in number of queries. It gets some list of values and return aggregate value.

For example (I simplify it, it is more complex in deed):

CREATE FUNCTION Mean(@N Numbers READONLY)
RETURNS TABLE AS RETURN (
    SELECT mean = SUM(n.value) / COUNT(*) FROM @N n
)

and I want to use it in query:

SELECT d.DepartmentName, MeanRate = m.mean
FROM Departments d
CROSS APPLY Mean(
    (
        SELECT value = e.Rate
        FROM Employees e
        WHERE e.DepatmentId = d.DepatmentId
    )
) m

But I get an error: Operand type clash: float is incompatible with Numbers

I know that I can use cursor or pass values as XML, but I think this ways are slower than inline function and table variables.

How can I pass a list of values to inline function?

Pரதீப் · Answer 1 · 2016-09-13T14:02:44.337

1

First you should create a table variable using the table type(Number) used in the Inline function.

Insert the required rows into table variable and pass the table variable o Inline function

You need to do something like this

declare @Numbers Numbers

Insert into @Numbers 
select e.Rate
From Employees  E join 
Departments d on e.DepatmentId = d.DepatmentId

select * from Mean(@Numbers)

Update : Based on your comments

Create a new table type.

CREATE TYPE Dept_number AS TABLE
(
DepatmentId  INT ,value numeric(22,6)
);

Alter the function

ALTER FUNCTION Mean(@dept_number DEPT_NUMBER readonly) 
returns TABLE 
AS 
    RETURN 
      (SELECT depatmentid, 
              mean = Sum(n.value) / Count(*) 
       FROM   @dept_number n 
       GROUP  BY depatmentid)

Calling the function

DECLARE @dept_number DEPT_NUMBER 

INSERT INTO @dept_number 
            (depatmentid, 
             value) 
SELECT d.depatmentid, 
       e.rate 
FROM   employees E 
       JOIN departments d 
         ON e.depatmentid = d.depatmentid 

SELECT * 
FROM   Mean(@dept_number)

edited Sep 13 '16 at 14:02

answered Sep 13 '16 at 13:54

Pரதீப்

91,748
19
131
172

But this query returns value for one department only, so I need to create cursor to get all of them. – Artem Sep 13 '16 at 13:55
@Artem - No, we are passing the value from all the departments. – Pரதீப் Sep 13 '16 at 13:56
Oh, I see. But I need a mean rate for each department separately. – Artem Sep 13 '16 at 13:57
@Artem - Hope it makes sense now – Pரதீப் Sep 13 '16 at 14:03
Now function Mean is not omni-purpose, because it expects DepatmentId, but I want to use it in many other queries that have no DepartmentId column. Moreover, some queries have not any int identifier at all. – Artem Sep 13 '16 at 14:23
@Artem - This is the efficient way i can think. If you cannot alter then you need loops. Or convert the values to csv and pass to function for each department which is again not efficient – Pரதீப் Sep 13 '16 at 14:25

How to pass user-defined table type to inline function

1 Answers1

Linked