Concatenate a value to a table in static sql

Question

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 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.

Thanks in Advance

Answer 1

The solution is to redesign the database. In the example, you add a year name to the table name. The year is data, and should be a column in the the table. And that's the table in singular. It is also a key to find your data, so it should be part of the primary key for the table, and probably it should be the leading key in the primary key.

If this solution does not please you, we need to know why you feel compelled to have many tables instead of one to discuss other options.

Answer 2

Hi @Varuni Rao ,

Welcome to the microsoft TSQL Q&A forum!

Why don't you want to use dynamic sql.If you want a pure tsql solution, it seems that it can only be achieved through dynamic sql.

Regards
Echo

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

If you cannot yet redesign the database, then check the next example, which executes a static query — ‘select * from mytab’ — where mytab denotes one of tables:

-- two sample tables

drop table if exists #table_2019, #table_2020

create table #table_2019 ( id int, val int)
create table #table_2020 ( id int, val int)

insert #table_2019 values (1, 100), (2, 101)
insert #table_2020 values (7, 200), (8, 201), (9, 202)


-- target table name

declare @table_name varchar(max) = '#table_2019' -- or '#table_2020'


-- redefine the synonym

drop synonym if exists mytab
declare @c varchar(max) = concat('create synonym mytab for ', quotename(@table_name))
exec (@c)


-- execute the query

select * from mytab

Therefore, instead of querying a fixed table, it uses a synonym, which is redefined before the query.

Maybe you can apply this technique in your new stored procedure with minor changes. However, it is probably not suitable for parallel execution without corresponding measures.