How do I fix Error: "Column alias error with SQL Server UDF"

Question

I'm trying to make a stored sql server function that will return a table of median values that I can join back to another table, thusly:

CREATE FUNCTION [dbo].getmedian (@varPartionBy1 int,@varPartionBy2 int, @varForTieBreak int, @varForMeasure int)
    RETURNS TABLE
    AS
    RETURN 
    (
        SELECT
           @varPartionBy1,
           @varPartionBy2,
           AVG(@varForMeasure) 
        FROM
        (
           SELECT
              @varPartionBy1,
              @varPartionBy2,
              ROW_NUMBER() OVER (
                 PARTITION BY @varPartionBy1, @varPartionBy2
                 ORDER BY @varForMeasure ASC, @varForTieBreak ASC) AS RowAsc,
              ROW_NUMBER() OVER (
                 PARTITION BY @varPartionBy1, @varPartionBy2 
                 ORDER BY @varForMeasure ASC, @varForTieBreak  DESC) AS RowDesc
            from 
            [fakename].[dbo].[temptable] bp
            
        ) bp
        WHERE 
           RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
        GROUP BY @varPartionBy1, @varPartionBy2
    
    )

GO

This is returning the error: "Msg 8155, Level 16, State 2, Procedure getmedian, Line 25

No column name was specified for column 1 of 'bp'." --indicating that I don't understand how to assign the table alias for a column in the context of a UDF, I guess.

What should I do to fix the error?

This is my very first UDF so I appreciate any other helpful design insights you have while addressing them main question. Thanks for any help!

Answer 1

Where you have SELECT @varPartionBy1, @varPartionBy2 those need to have column names assigned to them. You can either assign them directly such as SELECT @varPartionBy1 AS varPartionBy1 or SELECT varPartionBy1 = @varPartionBy1 or you can specify it in the table alias ) bp(varPartionBy1, varPartionBy2,...

The correct function would likely be

CREATE FUNCTION [dbo].getmedian (@varPartionBy1 int,@varPartionBy2 int, @varForTieBreak int, @varForMeasure int)
RETURNS TABLE
AS
RETURN 
(
    SELECT
       varPartionBy1,
       varPartionBy2,
       AVG(@varForMeasure) AS AvgVarForMeasure
    FROM
    (
       SELECT
          @varPartionBy1 AS varPartionBy1,
          @varPartionBy2 As varPartionBy1,
          ROW_NUMBER() OVER (
             PARTITION BY @varPartionBy1, @varPartionBy2
             ORDER BY @varForMeasure ASC, @varForTieBreak ASC) AS RowAsc,
          ROW_NUMBER() OVER (
             PARTITION BY @varPartionBy1, @varPartionBy2 
             ORDER BY @varForMeasure ASC, @varForTieBreak  DESC) AS RowDesc
        from 
        [fakename].[dbo].[temptable] bp

    ) bp
    WHERE 
       RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
    GROUP BY varPartionBy1, varPartionBy2

)