Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

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
0
TeDeSm
Asked:
TeDeSm
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]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 BurrellDirectorCommented:
Remove the brackets from around the select statement.
0
 
TeDeSmAuthor Commented:
That did the trick, but why are the brackets inserted. Are they needed at all?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
cyberkiwiCommented:
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
 
TeDeSmAuthor Commented:
Great help, I understand a bit more now, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now