sql-serveruser-defined-functionscolumn-alias

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


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!


Solution

  • 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
    
    )