Credit to @abolfazl-sadeghi as this is basically just a fixed up version of what he suggested - instead of trying to use EXEC inside the query, instead build a union query dynamically then EXEC once.

As already discussed, this is obviously not an ideal way to be doing things, but I appreciate you are constrained by the existing design.

To improve the resilience of this going forward, I have also added a join to the system schema to verify that the views actually exist and have a [Status] column. That way if someone drops a view, or adds a bad entry to the ordertypes table, it will still work. It wont currently handle the case where you have a variety of column types for [Status] - you could work around that by converting to varchar in the "TOP 1" query - but obviously then the output would be a varchar, and that might not be what you want. You could also constrain to a column type.

DECLARE @sql VARCHAR(MAX) = '';
SELECT @sql = STRING_AGG(' select ''' + [OrderTypeName] + ''',(SELECT TOP 1 [Status] from dbo.Order_' + [OrderTypeName] + ') as [Status]', ' union ')
FROM dbo.orderTypes ot
INNER JOIN INFORMATION_SCHEMA.COLUMNS c 
    ON c.TABLE_SCHEMA = 'dbo'
    AND c.TABLE_NAME = 'Order_' + [ot].[OrderTypeName]
    AND c.COLUMN_NAME = 'Status';
EXEC (@sql);

You need to put the whole thing into dynamic SQL, you cannot execute dynamic SQL in the middle of a query.

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG('
SELECT
  ' + QUOTENAME(OrderTypeName, '''') + ' AS OrderTypeName,
  (SELECT TOP (1) Status
   FROM dbo.' + QUOTENAME('Order_' + ot.OrderTypeName) + '
  ) AS Status
',' UNION ALL ')
FROM OrderTypes ot
JOIN sys.tables t ON t.name = 'Order_' + ot.OrderTypeName
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = 'dbo';

PRINT @sql;   -- your friend

EXEC sp_executesql @sql;

Note the use of QUOTENAME to correctly escape names.

You can use sp_executesql to pass parameters to the dynamic query.