asked on

Stored Procedure - SQL Block Order Syntax

I am receiving "Incorrect syntax near keyword ORDER" when saving the procedure after editing the SQL block. What would be the correct syntax? Without the ORDER BY the procedure is correct. IDPreAlert is the PK.

ALTER PROCEDURE dbo.floydsp_TreeViewCompanyFillCompany
      /*
      This sp returns all rows from table PreAlerts for a passed IDCompany
      */
      (
      @idcompany bigint
      )
AS
      (SELECT TOP (100) PERCENT IDPreAlert, IDCompany, CompanyName
       FROM PreAlerts
       WHERE (IDCompany = @idcompany)
       ORDER BY IDPreAlert)
RETURN

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dale Burrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TeDeSm

ASKER

That did the trick, but why are the brackets inserted. Are they needed at all?

Guy Hengel [angelIII / a3]

they are not needed.
the brackets make sql "think" it's a subquery (aka inline view) which would require an alias ...
and does not allow order by ...

cyberkiwi

What is inserting the brackets?
The bare minimum:

ALTER PROCEDURE dbo.floydsp_TreeViewCompanyFillCompany
/*
This sp returns all rows from table PreAlerts for a passed IDCompany
*/
@idcompany bigint
AS
SELECT IDPreAlert, IDCompany, CompanyName
FROM PreAlerts
WHERE IDCompany = @idcompany
ORDER BY IDPreAlert

All unnecessary brackets removed. TOP 100 PERCENT does nothing either.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TeDeSm

ASKER

Great help, I understand a bit more now, thanks.