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
TeDeSmAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
FWIW, your query works with the last bracket shifted one line up, if the select must have a bracket in whatever front-end tool you are using.

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
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
remove the () around the whole sql
also, return is not needed here..
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

Open in new window

0
 
Dale BurrellConnect With a Mentor DirectorCommented:
Remove the brackets from around the select statement.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
TeDeSmAuthor Commented:
That did the trick, but why are the brackets inserted. Are they needed at all?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 ...
0
 
cyberkiwiCommented:
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.
0
 
TeDeSmAuthor Commented:
Great help, I understand a bit more now, thanks.
0
All Courses

From novice to tech pro — start learning today.