Solved

Stored Procedure - SQL Block Order Syntax

Posted on 2010-09-06
7
313 Views
Last Modified: 2012-05-10
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
Comment
Question by:TeDeSm
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 160 total points
ID: 33610238
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
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 160 total points
ID: 33610245
Remove the brackets from around the select statement.
0
 

Author Comment

by:TeDeSm
ID: 33610384
That did the trick, but why are the brackets inserted. Are they needed at all?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33610399
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33610411
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 180 total points
ID: 33610423
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
 

Author Closing Comment

by:TeDeSm
ID: 33610838
Great help, I understand a bit more now, thanks.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question