sql server function to calculate median

Question

I'm trying to create function to calculate the median of 3 values, but get few errors- " Stmt within function cannot return data to client"

CREATE FUNCTION median(@value1 float,@value2 float,@value3 float)
RETURNS float
AS
BEGIN
    DECLARE @ret float;
    DECLARE @CNT int = 3;

    select cast(avg(tb1.val*1.0) as float) as @ret
    from 
       (select 
          val, ROW_NUMBER() over (order by val) as rid 
        from 
            (select @value1 val
         union 
             select @value2 val
         union
             select @value3 val) TB1
        where tb1.rid in ((@cnt + 1) / 2, (@cnt + 2) / 2)

    return @ret;
end;

Answer 1

Your syntax is wrong. You're selecting a result set in the middle of the function rather than assigning the value to the local variable, hence the error. Try this instead:

...
set @ret = (select cast(avg(tb1.val*1.0) as float) 
    from (select val, ROW_NUMBER() over (order by val) as rid from 
            (select @value1 val
            union 
            select @value2 val
            union
            select @value3 val) TB1
    where tb1.rid in ((@cnt+1)/2,(@cnt+2)/2)
    )
...

Answer 2

CREATE FUNCTION medianx(@value1 float,@value2 float,@value3 float)
RETURNS numeric(10,2)
AS
    BEGIN
        DECLARE @ret float;
        DECLARE @CNT int = 3;
        select @ret=cast(avg(tb1.val*1.0) as numeric(10,2)) 
            from (select val, ROW_NUMBER() over (order by val) as rid from 
                (select @value1 val
                union 
                select @value2 val
                union
                select @value3 val) TB1) TB1
        where tb1.rid in ((@cnt+1)/2,(@cnt+2)/2)



    RETURN @ret
    end;

Answer 3

In the case, when one or more of the input parameters may have NULL value:

create function median(@v1 float, @v2 float, @v3 float)
returns float
as
begin
    declare @median float;

    ;with D(V, N) as (
        select V, row_number() over (order by V)
        from (values (@v1), (@v2), (@v3)) T(V)
        where V is not NULL)
    ,C(Cnt) as (select count(1) from D)
    select @median = (
        select avg(V) as Median
        from C, D
        where C.Cnt % 2 = 1 and N = C.Cnt/2 + 1
            or C.Cnt % 2 = 0 and N in (C.Cnt/2, C.Cnt/2 + 1))

    return @median;
end

Above function can be written in more general form, allowing set of more than three values be passed as a table parameter:

create type FloatData as table (Value float)
GO
create function median(@data as FloatData readonly)
returns float
as
begin
    declare @median float;

    ;with D(V, N) as (
        select Value, row_number() over (order by Value)
        from @data
        where Value is not NULL)
    ,C(Cnt) as (select count(1) from D)
    select @median = (
        select avg(V) as Median
        from C, D
        where C.Cnt % 2 = 1 and N = C.Cnt/2 + 1
            or C.Cnt % 2 = 0 and N in (C.Cnt/2, C.Cnt/2 + 1))

    return @median;
end
GO