SQL Procedure that will calculate the Last Price paid for a certain Part
Considering that your subselect is basically the same as your main select, chances are that this subselect is indeed a bit of overkill. Looking at your logic, what you want to achieve is to get the Price of the SalesOrderDetail linked to the SalesOrderHeader with the most recent OrderDate, so let's use an ORDER BY DESC and select only the first answer.

I think this should do the trick:

SELECT TOP 1 SD.Price
FROM SalesOrderHeaders AS SH
INNER JOIN SalesOrderDetails AS SD
    ON SH.Id = SD.HeaderId
WHERE SD.LineItemId = @ItemId
ORDER BY SH.OrderDate DESC