CODEWITHSUNDEEP
sqlsql-server
Mamen
Mamen

Reputation: 1436

Error Order by clause in sql server function

i have sql server query function with join to other tables and i got some error

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

    CREATE FUNCTION [dbo].[GetMutationUnGrouped]     
    (   
        -- Add the parameters for the function here
        @startDate Date, 
        @endDate Date,
        @PlaceId nvarchar(128)



    )
    RETURNS TABLE 
    AS
    RETURN (
        SELECT mut.*,inv.Barcode, itm.ItemName,plc.PlaceName,loc.ItemLocationName,sub.ItemSubLocationName,inv.BuyDate,inv.Cost,typ.TypeName

        FROM ItemInventoryMutation mut 
        JOIN ItemInventory inv ON mut.ItemInventoryId = inv.Id
        JOIN Items itm ON inv.itemId = itm.Id
        JOIN ItemTypes typ ON itm.ItemTypesId = typ.Id
        JOIN ItemSubLocations sub ON mut.ItemSublocationId = sub.Id
        JOIN ItemLocations loc ON sub.ItemLocationsId = loc.Id
        JOIN Place plc ON loc.PlaceId = plc.Id


        WHERE (inv.BuyDate >= @startDate AND inv.BuyDate <= @endDate) AND  (inv.itemId IS NOT NULL AND inv.IsDelete = 0) AND loc.PlaceId = @PlaceId
        ORDER BY mut.Id ASC, mut.MutationDate DESC

    )

what is causing the error? thank you

Upvotes: 0

Views: 168

Answers (1)

Szymon
Szymon

Reputation: 43023

Well, this is exactly what the error message says - you cannot use ORDER BY in your return table definition unless you specify TOP.

I'm guessing you don't want to specify TOP in this case and want to return all records. In this case, you should return not ordered records from your [dbo].[GetMutationUnGrouped] function and order them when you use the function.

You can find more info on MSDN:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

So if you wanted to get ordered records from your function, that won't work - you will have to order records again anyway, even if you specify TOP in your function.

Upvotes: 1

Related Questions