current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
    
What RDBMS are you using? –  LittleBobbyTables Aug 30 '12 at 17:49
    
The best way is to use "Dynamic SQL" –  Praveen Aug 30 '12 at 17:50

2 Answers 2

up vote 0 down vote

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
share|improve this answer
up vote 1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.