SQL Server order by in procedure-CodePudding

I have the following query which works fine in SQL Server:

SELECT InvoiceID, SUM(UnitPrice)
FROM Sales.InvoiceLines
GROUP BY InvoiceID
ORDER BY InvoiceID

When I put it in a procedure the order by clause is not accepted:

CREATE OR ALTER PROCEDURE dbo.ReportsSales
AS
    SET NOCOUNT ON
    BEGIN (
        SELECT InvoiceID, SUM(UnitPrice)
        FROM Sales.InvoiceLines
        GROUP BY InvoiceID
        ORDER BY InvoiceID)
END;

EXEC dbo.ReportsSales;

Why is that and is there any recommended way of dealing with it?

CodePudding user response：

You must not use round brackets around the SELECT in the procedure:

CREATE OR ALTER PROCEDURE dbo.ReportsSales
AS
BEGIN
    SET NOCOUNT ON;

    SELECT InvoiceID, SUM(UnitPrice)
    FROM Sales.InvoiceLines
    GROUP BY InvoiceID
    ORDER BY InvoiceID;
END;
GO

-- execute it
EXEC dbo.ReportsSales;

Then you should be fine.