title: Error unexpected token in stored procedure Advantage Database SQL
body: <p>I have A stored procedure that is giving me an unexpected Token; ORDER expecting semicolon when I have this statement near the end when I try to execute it. </p>
  
  <p><strong>select year from #temp where year is not null ORDER BY year DESC;</strong></p>
  
  <p>if I remove the <strong>ORDER BY year DESC;</strong> the procedure works correctly.</p>
  
  <p>I've tried every way possible to sort the resulting table in descending order.  I'm fairly new to SQL so I'm sure its something simple.  TIA.</p>
  
  <p>// --------- full stored procedure ------ //</p>
  
  <pre><code>ALTER PROCEDURE GetYearForExhaustCatalog
     ( 
        CatCodeString Memo,
        Year CHAR ( 4 ) OUTPUT
     ) 
  BEGIN 
  /*
  
    EXECUTE PROCEDURE GetYearForExhaustCatalog('(e.catalogcode= ''2182'')');
  
    EXECUTE PROCEDURE GetYearForExhaustCatalog('');
  
  
  */  
  
  DECLARE @CatCodeString string;
  DECLARE @SQL string;
  
  @CatCodeString = (SELECT CatCodeString FROM __input);
  
  if @CatCodeString IS NULL or @CatCodeString = '' then
    select e2.year,
    (SELECT top 1 e2.year
    FROM  eenginecatalog e LEFT JOIN exhaustengine e2 ON e2.app_no=e.app_no)
    as year
    into #temp
    from Exhaustengine e2;
    select year from #temp where year is not null
    GROUP BY  year
    ORDER BY  year DESC;
  else
    @SQL = 
      'select e2.year, '+ 
      '(SELECT top 1 e2.year '+
      'FROM  eenginecatalog e LEFT JOIN exhaustengine e2 ON e2.app_no=e.app_no and '+
         @CatCodeString +' ) '+ 
      'as year '+
      'into #temp '+
      'from Exhaustengine e2; '+
      'select year from #temp where year is not null '+
      'GROUP BY  year '+
      'ORDER BY  year DESC ';
    execute immediate @SQL;
  end;
  
  insert into __output
    select year from #temp where year is not null ORDER BY year;
    drop table #temp;
  
  END;
  </code></pre>