Use a String variable in a from Statement

Question

I want to be able to pass a parameter to a Query to give the names of the tables I am using.

DECLARE @ImportedTable char(100) 
DECLARE @EnvTable char(100) 

SET @ImportedTable = 'tmpImported'
SET @EnvTable = 'tblDestination'

PRINT @ImportedTable

Select *

From @ImportedTable

When I print the Variable, it looks fine, but when I run the whole thing, the From Clause gives the following error

Must declare the table variable "@ImportedTable".

Why won't it see the variable contents in From Clause?

SQL Server does not support macro substitution. This would require Dynamic SQL — John Cappelletti, Aug 15 '22 at 19:00
Great, thank you I just tried that and got it working appreciate your help — ScottUK, Aug 15 '22 at 19:07

score 1 · Answer 1 · edited Aug 16 '22 at 00:11

As stated, you need dynamics SQL. Something like:

Declare @EnvTable sysname;
Declare @sql nvarchar(max);
Set @ImportedTAble = 'tmpImported'
Set @EnvTable = 'tblDestination'
Set @sql = N'Select * From ' + QUOTENAME(@ImportedTable);

Exec sp_executesql @sql;

Dynamics SQL DOES bring issues into code, though, including SQL injection, not using query plans, and more. So, make sure you read up on dynamic sql and how to mitigate the issues it brings.

Use a String variable in a from Statement

1 Answers1