Using a local variable in the from statement in SQL

Question

I have a database where each table is named by standard conventions. I am building a SQL script to run periodically on various sets of similarly named tables.

I would like to declare a local variable that contains common part of table names between the tables and use that variable to fill in a part of the table name in FROM statement of my SELECT statements.

How could I abstract this to run on a table where 1234 is substituted out for the value of a local variable?

Select count(*) From [Database].[dbo].[Table_1234]

This obviously doesn't work:

Select count(*) From [Database].[dbo].['Table_'+@variable]

Is there a way to use local variables as table names in select statements or a way around this?

The best way is to use "Dynamic SQL" – Praveen Aug 30 '12 at 17:50 — Praveen, Aug 30 '12 at 17:50

score 2 · Answer 1 · answered Aug 30 '12 at 17:52

2

Disclaimer: This looks like SQL Server to me, but the question was not tagged as such.

You can use the EXEC function:

EXEC('Select count(*) From [Database].[dbo].[Table_' + @variable + ']')

SQL Fiddle: http://www.sqlfiddle.com/#!3/0ef1d/3

answered Aug 30 '12 at 17:52

Mike Christensen

88,082
50
208
326

score 0 · Answer 2 · answered Aug 30 '12 at 18:04

I don't see how to get around the dynamic SQL. But I would recommend minimizing it by using synonyms (assuming you will be running multiple/complex queries instead of a simple row count):

DECLARE @var NVARCHAR(8)

SET @var = '1234'

EXEC('create synonym synTable1 for Table1_' + @var)
EXEC('create synonym synTable2 for Table2_' + @var)
EXEC('create synonym synTable3 for Table3_' + @var)

SELECT COUNT(*) FROM synTable1
...

DROP SYNONYM synTable1
DROP SYNONYM synTable2
DROP SYNONYM synTable3

Using a local variable in the from statement in SQL

2 Answers2