The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries

Question

my stored procedeur like this:

alter PROCEDURE [dbo].[ParkingDeatailsReportnewstack] 
      @startdate NVARCHAR(100),
      @enddate NVARCHAR(100)AS
BEGIN
    DECLARE  @cols AS NVARCHAR(MAX) , @query AS NVARCHAR(MAX)

    SELECT @cols = STUFF((  SELECT DISTINCT ',' + QUOTENAME(Vtype)
    FROM dbo.VType_tbl FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

    SET @query =
        'SELECT  LocName,Date, ' + @cols + '
         from  ( 
                select  l.LocName,v.Vtype, convert(date, dtime) as Date 
                from Transaction_tbl t 
                join VType_tbl v on t.vtid = v.vtid 
                join dbo.Location_tbl l on t.locid=l.Locid
                where dtime between ''' + @startdate + ''' and ''' + @enddate + '''  order by l.LocName
         ) d
         pivot (
                count(Vtype) for Vtype in (' + @cols + ')
         ) p '  

    EXEC sys.sp_executesql @query
End

i want to get my locname in ascending order but while giving order by l.LocName getting error :The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries

Have you tried moving the `ORDER BY LocName` after your pivot? So after the `) p `? — Taryn, Aug 06 '13 at 16:54
For what it's worth, stored procedures don't require `BEGIN` and `END` statements. — ErikE, Aug 06 '13 at 16:59
i try to move order by after p that time i am getting error like this"The multi-part identifier "l.LocName" could not be bound — user2648161, Aug 06 '13 at 17:03
And please fix the spelling of your proc name or the people doing maintenance will curse your name forever. — HLGEM, Aug 06 '13 at 17:19
Another issue for you: you might want to consider how you're constructing your SQL. That sort of dynamic SQL leaves you open to SQL injection attacks. Whether that's a potential issue for you, only you know. — Nicholas Carey, Aug 06 '13 at 17:28
possible duplicate of [How to ORDER BY in SQL PIVOT](http://stackoverflow.com/questions/13755017/how-to-order-by-in-sql-pivot) — Nicholas Carey, Aug 06 '13 at 17:29
If you don't know how to move an ORDER BY clause from a subquery to the outer query properly, I doubt very much you know what you are doing with this query at all. This is not to intimidate you, I just think you may be trying to learn too many things at a time. Consider taking on easier problems, so that you can understand better what you are doing, and why. — Andriy M, Aug 06 '13 at 17:44

score 3 · Answer 1 · answered Aug 23 '16 at 07:39

3

We can fix the problem very easily by adding a TOP 100 PERCENT clause into the view definition. Try using this way - http://www.sqlpassion.at/archive/2015/05/25/the-ambiguity-of-the-order-by-in-sql-server/

answered Aug 23 '16 at 07:39

Rohit Suthar

3,528
1
42
48

1

Better solution when you can't extract your order by to an outer query. Thanks! – Alfredo A. Nov 12 '18 at 16:47

Nicholas Carey · Accepted Answer · 2013-08-06T17:25:43.073

2

Try migrating your order by clause into the outermost select statement — the only place where order by makes any sense.

Something like

select LocName ,
       ...
from ...
order by LocName

Or, even simpler, wrap the original, complex select in an outer select whose sole purpose is ordering, along these lines:

select *
from ( select
       ...   
     ) t
order by t.x, t.y , ...

edited Aug 06 '13 at 17:25

answered Aug 06 '13 at 16:55

Nicholas Carey

71,308
16
93
135

see my amended answer. – Nicholas Carey Aug 06 '13 at 17:25

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries

2 Answers2

Linked