Solved

Stored Procedure - SQL Block Order Syntax

Posted on 2010-09-06
7
337 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

632 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