Passing a variable to a Table name the is partially written

Question

I want to pass a variable to a table name in SQL Server. I have tables that have date stamps on them, followed by an alpha character:

EO_PurcheseOrder_11252022P

EO_PurcheseOrder_12022022P

EO_PurcheseOrder_12152022P

EO_PurcheseOrder_12202022P

etc.

More tables are added in this format every week. I want to be able to write a header at the top of my code where the user can input a date and then the code will add the MMDDYYYY throughout the code in the table names.

I want to start my code with a

Declare @Table_Date INT;

set @Table_Date = 12152022

or

Declare @Table_Date VARCHAR(9);

set @Table_Date = 12152022P

But how would I pass that @Table_Date to the table name in the code? An example would be:

select * 
  into DRX.earlyorderNJ_DX_2022
  from DRX.EO_PurcheseOrder_11252022P

You would have to use dynamic SQL. You cannot pass a variable in a referenced table name. Read up on [Dynamic SQL](https://www.sqlshack.com/dynamic-sql-in-sql-server/). — Tim Jarosz, Dec 30 '22 at 13:42
Separate tables with a timestamp has a bit of a code smell. Dynamic SQL example: `DECLARE @SQL nvarchar(MAX) = N'select * into DRX.earlyorderNJ_DX_2022 from DRX.' + QUOTENAME(N'EO_PurcheseOrder_' + @Table_Date) EXEC sp_executesql @SQL;` — Dan Guzman, Dec 30 '22 at 13:47

Tim Jarosz · Answer 1 · 2022-12-30T14:34:43.133

0

You have to use dynamic SQL to reference a table with a variable. Here's a quick example:

--Declare your date variable.
DECLARE @Table_Date date;
SET @Table_date = DATEFROMPARTS(2022,12,15);

DECLARE @Table_name nvarchar(50);
SET @Table_name = QUOTENAME('EO_PurchaseOrder_' + FORMAT(@Table_Date , 'ddMMyyyy') + 'P');

--Create dynamic SQL by adding the date to the end of a table name.
DECLARE @sqlText nvarchar(max) = '
  SELECT *
  FROM DRX.' + @Table_name + ';
';

--Execute the dynamic SQL.
EXEC sys.sp_executesql @sqlText;

edited Dec 30 '22 at 14:34

answered Dec 30 '22 at 13:46

Tim Jarosz

1,133
1
8
15

Careful, there aren't 15 months in the year; use unambiguous date formats. Also is you are *injecting* an object name into a dynamic statement, you should really be using `QUOTENAME`, and you should be using `sys.sp_executesql` to execute the statement, not `EXEC(@SQL)` as such statements cannot be parametrised, which promote bad habits that result in security flaws like SQL injection. – Thom A Dec 30 '22 at 14:19
@Larnu points taken. Answer Updated. – Tim Jarosz Dec 30 '22 at 14:27
That isn't how you use `QUOTENAME` to inject. `QUOTENAME` needs to wrap the injected object's name. – Thom A Dec 30 '22 at 14:28
Then I need a lesson as well. Same with ambiguous dates... I'm in USA so my localization takes the date no problem but I understand this is an international audience. – Tim Jarosz Dec 30 '22 at 14:30
Yes, [American] English is the *only* country that puts the month *before* the day in SQL Server (and, in truth, in written languages). Assuming that the user is using [American] `ENGLISH` as their language setting can have fatal consequences. – Thom A Dec 30 '22 at 14:32

Passing a variable to a Table name the is partially written

1 Answers1