Loop query through multiple tables

I would like to the same query which should almost never return any results through multiple tables. The table name is the same except with the addition of the year. So my query looks like this:

select p.productID, po.name, p.price from prices_2001  p
join products po on p.productID = po.id
where price < 0

and I would like to loop through all the years from 2001 to 2020. So it should look like this:

select p.productID, po.name, p.price from prices_2001  p
join products po on p.productID = po.id
where price < 0

select p.productID, po.name, p.price from prices_2002  p
join products po on p.productID = po.id
where price < 0

select p.productID, po.name, p.price from prices_2003  p    --Looping trough all the tables until 2020
join products po on p.productID = po.id
where price < 0

If there are results they can be stored in a temp table. I thought of creating a loop with the table names or creating dynamic sql query. What is the best practice and how can I use this query for all the tables just by executing it once?

Thank you!

Answer

To reiterate my comment:

This sounds like a denormalisation problem, really you should have 1 table with a column for the year.

Really, you should be fixing your design.

Anyway, onto the problem: You can do this with dynamic SQL, yes, but you’d be better off fixing the design. This however, create and executes the statement dynamically. i’ve used FOR XML PATH due to a lack of version tag:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);


SET @SQL = STUFF((SELECT @CRLF + N'UNION ALL' + @CRLF +
                         N'SELECT p.productID, po.name, p.price' + @CRLF +
                         N'FROM ' + QUOTENAME(t.[name]) + N'p' + @CRLF +
                         N'     JOIN product po ON p.productID = po.id' + @CRLF +
                         N'WHERE p.price < 0'
                  FROM sys.tables t
                  WHERE t.[name] LIKE N'prices[_]20[0-9][0-9]'
                  ORDER BY t.[name]
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,13,N'') + N';';

--PRINT @SQL; --Your debugging friend

EXEC sys.sp_executesql @SQL;