'SQL Server order by in procedure [closed]

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?

sqlsql-servertsql


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions