How To Pass User-defined Table Type To Inline Function

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

Solution 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

Insertinto@Numbersselect 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 ASTABLE
(
DepatmentId  INT ,valuenumeric(22,6)
);

Alter the function

ALTERFUNCTIONMean(@dept_number DEPT_NUMBER readonly) 
returnsTABLEASRETURN 
      (SELECT depatmentid, 
              mean = Sum(n.value) / Count(*) 
       FROM   @dept_number n 
       GROUP  BY depatmentid) 

Calling the function

DECLARE@dept_number DEPT_NUMBER 

INSERTINTO@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)