title: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries
body: <p>my stored procedeur like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 </p>