This need is often an indication of a poor datamodel. E.g. you have multiple tables with the same type of content. In that case it is better to have one table with an added type column. It would better to adress that contingency first.
The problem with your current code is the apostrophes surrounding the table name.
set @query = 'Select Products.*," + @TableName + ".*
From Products INNER JOIN " + @TableName + " on Products.ProductID = " + @TableName + ".ProductID
WHERE Products.ProductID = " + @strProductID