Solved

Stored Procedure - SQL Block Order Syntax

Posted on 2010-09-06
7
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
Sub form showing data is being saved but cannot be displayed.. 31 69
sql trace 4 28
SQL Simple Query Taking a Very Long Time 11 37
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 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