Use of variable table name in Query

Question

From time to time I need to run the same SQL Server script, every time changing one or two letters in each table name to a value which is different each time I run the script.

How can I set once at the beginning of the script, so that I do not need to change each table name?

Here is a simplified example of the script, which in reality contains a lot more tables:

SELECT * FROM T_Atable1 
    WHERE ref IN (SELECT ref FROM T_Atable2)
         AND customer IN (SELECT customer FROM T_Atable3)

Table names are of the form T_ where:

"T_" is fixed
<id> is the variable part of the table name. In the above example = "A"
<table name> is the fixed name of the table. In the above example, the fixed parts are "table1", "table2", "table3"

I suspect I need to use dynamic SQL but do not know how to do this.

SQL Server could be any version 2003+, depending on the system containing the data.

Does this answer your question? [A table name as a variable](https://stackoverflow.com/questions/2838490/a-table-name-as-a-variable) — GSerg, Nov 11 '22 at 00:36

score 3 · Accepted Answer · answered Jul 03 '14 at 05:46

3

You'll need to use Dyanmic SQL. Dynamic SQL is simply that you build your query "dynamically" in a string either in your SQL procedure or in an application, and then you execute that string.

For example;

DECLARE @tableVar1 VARCHAR(255) = 'T_Atable1'
DECLARE @tableVar2 VARCHAR(255) = 'T_Atable2'
DECLARE @tableVar3 VARCHAR(255) = 'T_Atable3'


EXEC('
    SELECT * FROM ' + @tableVar1 + '
    WHERE ref IN (SELECT ref FROM ' + @tableVar2 + ')
    AND customer IN (SELECT customer FROM ' + @tableVar3 + ')
')

answered Jul 03 '14 at 05:46

Allan S. Hansen

4,013
23
25

I don't think `exec` allows you to concatenate an SQL string in-place. `ecec('select' + ' 1')` gives an error. – Andomar Jul 03 '14 at 05:50
@Andomar : It does; `DECLARE @tableVar1 VARCHAR(255) = 'A'; EXEC('SELECT * FROM ' + @tableVar1)` runs on 2008/2012, modified for 2005 and 2000 it also runs there (cannot assign in same line on 2005 and lower). sqlfiddle link: http://sqlfiddle.com/#!3/f82714/1 – Allan S. Hansen Jul 03 '14 at 05:55
You're right, the problem I remember is with `exec sp_executesql 'select' + ' 1'`. With `exec` it works fine. – Andomar Jul 03 '14 at 06:03

score 1 · Answer 2 · answered Jul 03 '14 at 05:46

A variable table name requires dynamic SQL:

declare @table sysname
set @table = 'A'

declare @sql nvarchar(max)
set @sql = 'select * from T_' + @table + 'table1 '  +
    '    WHERE ref in IN (SELECT ref FROM T_' + @table + 'table2) ' +
    '          AND customer IN (SELECT customer FROM T_' + @table + 'table3)'

exec (@sql)

score 1 · Answer 3 · answered Jul 03 '14 at 05:59

--Just change a prefix, don't worry about sql query:

-- Where @prefix is a common a whatever is prifix of table
declare @prefix varchar(50); set @prefix='T_A';  
-- Here your query in string format      
declare @sql varchar(max); set @sql='';
set @sql='SELECT * FROM ' + @prefix + 'table1 WHERE ref IN (SELECT ref FROM ' + @prefix + 'table2)' +
+ ' AND customer IN (SELECT customer FROM ' + @prefix + 'table3)';
--execute your query
exec(@sql);

score 0 · Answer 4 · answered Jul 03 '14 at 05:49

0

Dynamic Sql Query:

declare @sqlcommand nvarchar(1000)

set @sqlcommand='<your sql query>'


exec(@sqlcommand)

answered Jul 03 '14 at 05:49

Nadendla

712
2
7
17

Jesuraja · Answer 5 · 2014-07-03T06:16:12.577

0

Try this:

DECLARE @SQL VARCHAR(8000)
DECLARE @Part VARCHAR(10) = 'A'

SET @SQL = 'SELECT * FROM T_' + @Part + 'table1 
            WHERE  ref IN (SELECT ref FROM T_' + @Part + 'table2) AND
                   customer IN (SELECT customer FROM T_' + @Part + 'table3)'

EXEC (@SQL)

edited Jul 03 '14 at 06:16

answered Jul 03 '14 at 06:02

Jesuraja

3,774
4
24
48

Use of variable table name in Query

5 Answers5