Sql-server – Table concatenation

join;sql servertable

I want to have a dynamic query that I can concatenate my table based on current month. I want to have a variable to my FROM and JOIN statements.

DECLARE @month_number AS VARCHAR(2);
DECLARE @table_name AS VARCHAR(15);
DECLARE @table AS VARCHAR(MAX);
SET @month_number = (SELECT month(getdate()));
SET @table_name = 'Sessions_month_'
SET @table = @table_name+@month_number;

SELECT * FROM Sessions_month_+@month_number where id = (SELECT id FROM Sessions_month_+@month_number WHERE Contact = 2);

Or should I do this:

https://stackoverflow.com/questions/19939380/how-to-form-a-table-name-from-concatenating-strings-in-select-statement-in-sql-2

Best Answer

I am not here to judge whether it is a good idea to use dynamic sql, but just for your question, you may use the following code to reach your goal

DECLARE @month_number AS VARCHAR(2);
DECLARE @table_name AS VARCHAR(15);
DECLARE @table AS VARCHAR(MAX);
declare @qry nvarchar(max);
SET @month_number =  month(getdate());
SET @table_name = 'Sessions_month_'
SET @table = @table_name+@month_number;

set @qry='SELECT * FROM Sessions_month_' + @month_number +' where id = (SELECT id FROM Sessions_month_'+@month_number +' WHERE Contact = 2);'
exec sp_executesql @qry;
go
Related Question