0

I have a requirement in which i need to concatenate a value to table name. The requirement is as follows:

DECLARE @TableName varchar(50);

SET @TableName = (SELECT 'TableName' + CONVERT(varchar(50), 2019));

PRINT @TableName;

SELECT * FROM @TableName

I don't want to use dynamic sql query Can anybody suggests the solution to this problem.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user3767459
  • 3
  • 2
  • You can't do this in "static" SQL - if you **must** parametrize the table name, there's no way around using dynamic SQL – marc_s May 15 '21 at 12:44
  • Depending on what The Real Problem℠ is, you might benefit from [synonyms](https://learn.microsoft.com/en-us/sql/relational-databases/synonyms/synonyms-database-engine?view=sql-server-ver15). Or hiding the translation in a [view](https://learn.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-server-ver15). Or, better yet, getting the requirement "fixed". – HABO May 15 '21 at 13:20
  • Do you have at least a fixed list of tables to choose from? You really should fix your design, a single table with a `year` column – Charlieface May 15 '21 at 23:32

3 Answers3

2

You have a problem if you have tables that encode a year. You should have only one table with all the data. Then you can just select the year:

select t.*
from t
where year = 2019;

That said, sometimes you are stuck with other people's really, really, really bad decisions. If so, the code looks like:

DECLARE @SQL nvarchar(MAX);

SET @SQL = CONCAT('SELECT * FROM TableName', 2019);

EXEC SP_EXECUTESQL @SQL;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for reply. I know this can be achieved through dynamic sql but the challenge is stored procedure has almost 1500 lines so converting all into dynamic sql is not possible due to data type conversion errors. – user3767459 May 15 '21 at 15:13
  • @user3767459 . . . **Fix your data model.**. You are going to have to rewrite that stored procedure anyway, so you might as well address technical debt. – Gordon Linoff May 15 '21 at 18:26
0

Will this work for you ?

select table_name + '_' + CONVERT(varchar(50), round(rand()*1000,0) ) from information_schema.tables

Abhilash S
  • 31
  • 1
  • 4
  • Thanks for reply. There are multiple tables with same name but year is different so we need to pass year as parameter which is fetched from code-behind. – user3767459 May 15 '21 at 15:17
0

Create a view with all possible tables - adding a year column. Then use that view to query based on year.

Create View dbo.YearView As
Select Year = 2018, {all other columns} From dbo.Table2018
UNION ALL
Select Year = 2019, {all other columns} From dbo.Table2019
UNION ALL
Select Year = 2020, {all other columns} From dbo.Table2020

Use it as:

Select * From dbo.YearView Where Year = 2019;

You can then build a procedure using dynamic SQL that rebuilds this view with all possible tables and schedule that code to run once a year after the new years table has been created.

Jeff
  • 512
  • 2
  • 8