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.

select year from #temp where year is not null ORDER BY year DESC;

If I remove the ORDER BY year DESC; the procedure works correctly.

I've tried to sort the resulting table in descending order.

// --------- full stored procedure ------ //

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;