How to dynamically change the table you are selecting from?

Transact-SQL
1

Good morning! Was hoping you all could help me out. I currently have a stored procedure that takes in an int variable (a four digit year specifically). It then uses this year to search a huge database of records. The database has multiple tables, with one table containing all the data, while the rest of the tables have the same data but split up by one decade for each table. What I would like to do, in order to increase speed, is have the stored procedure look at the date, figure out what decade it is in, and then select from that specific decade table, as opposed to table containing all the data (which takes much longer). I really wanted to just make the table name a variable that I could change depending on the date (so something like "SELECT TOP 20 FROM @variable WHERE..."), but I found out that this is illegal and a bad idea. Is there any clean way to do this that wouldn't involve me copying the select statement 20 times for each of the decade tables (which gets messy and ugly really fast, since the select statement is pretty long). Thanks!

2

You have two main choices.

Easiest is to use dynamic SQL; which means building a sql string and the running it.
DECLARE @sql nvarchar(max)
SET @sql = N'SELECT TOP (20) FROM ' + @variable + ' WHERE ...'
EXEC(@sql)

1 Like
3

Thanks! I was aware of dynamic SQL, but your comment made me give it another look and I managed to get it working the way I wanted. Thanks again!

4

With SQL injection in mind, this will be safer:

DECLARE @sql nvarchar(max);
SELECT @sql = N'SELECT TOP (20) FROM ' + @variable + ' WHERE ...'
WHERE EXISTS (SELECT 1 FROM sys.tables WHERE [name] =  @variable);
EXEC(@sql);

ps SELECTing from a different table depending on date seems like bad design. You may of course just have to live with what is provided.

2 Likes
5

Yeah, unfortunately I have to work with what I have. The table structure is already set. But thanks for the input.

6

Actually - you have a third option. Build a view that performs a UNION ALL for the individual decade tables including a filter on the year column. For example:

CREATE VIEW dbo.FullView AS

SELECT ...
  FROM Table2000
 WHERE year BETWEEN 2000 AND 2009
 UNION ALL
SELECT ...
  FROM Table2010
 WHERE year BETWEEN 2010 AND 2019
 UNION ALL
 SELECT ...
  FROM Table2020
 WHERE year BETWEEN 2020 AND 2029;

When you select from this view - include the year column as such:

SELECT ...
  FROM dbo.FullView
 WHERE year = 2022;

I assume each individual table has an index on the year column - ideally it should be part of the clustered index. SQL will then be able to filter out of the final query any that don't meet the year requirement so you end up selecting only from the one table containing that years data.

7

Also, add a CHECK constraint to the table to limit the years that can go into that table. SQL can use constraint to eliminate tables from being read. In that case, you would want to include the year check in the table along with the date range check above.