Use variable as part of tablename

I have a set of reports that are run over 4 different companies in the same database.
They are identical except in each case the table names use a different company code.
e.g.
dbo.[UK$Sales Invoice Header] is used as one table for the UK company report
dbo.[FR$Sales Invoice Header] is used as one table for the FR company report
dbo.[DE$Sales Invoice Header] is used as one table for the DE company report

I have over 20 tables joined and would like to simplify by using a variable for the company part of the table names similar to

DECLARE @Co varchar(2) ='UK';
Then use
dbo.[@Co$Sales Invoice Header] in the select statement

Is it possible to use a variable as part of a table name?

Thanks.
Paul GAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Paul GConnect With a Mentor Author Commented:
Thanks you all for your input. I thought there may be a simple solution.
Find and Replace seems the way to go.

Thanks.
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Couple of choices
(1)  Use dynamic SQL to build the query in the SP, handling the @Co.  Last I remember this is not a good choice, as it has problems with SSRS/SSIS making the 'contract' with the SP for what the schema is, and prevents a good mapping.
(2)  Use about 20 different IF @Co='Country code' THEN statements, copying the query except for hard-coding the appropriate table name.   Quick and dirty, but also hard-coding in a SP.
(3)  If the 20 or so tables are the same, a modeling argument can be made that they should either be one table with an extra column for the country code.
(4)  Same as above, and if you can get away with creating a parameterized view, build one that handles the @co correctly, and use that as the data source.
(5)  If you can't get away with (4), build a query that UNIONs all 20 tables, with an extra column for country code.  Will be 20x the amount of data moving across, so avoid if possible.
0
 
Randy Knight, MCMPrincipal ConsultantCommented:
You said reports so I assume this is SSRS?  If so, I would use an expression in SSRS that dynamically builds the SQL statement based on a parameter.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
ZberteocConnect With a Mentor Commented:
You can use it only if the entire query is built dynamically in a variable:
declare 
	@table_prefix varchar(5)='',
	@sql varchar(max)=''

select
	@table_prefix='UK'
select 
	@sql='
SELECT TOP 10 * FROM dbo.['+@table_prefix+'$Sales Invoice Header]'
print @sql	/*	-- when you want to execute comment this line only
EXEC(@sql)	--*/


select
	@table_prefix='FR'
select 
	@sql='
SELECT TOP 10 * FROM dbo.['+@table_prefix+'$Sales Invoice Header]'
print @sql	/*	-- when you want to execute comment this line only 
EXEC(@sql)	--*/

Open in new window

0
 
ZberteocConnect With a Mentor Commented:
You can create a stored procedure for that:
create procedure dbo.spDynamicCompanyQuery
	@table_prefix varchar(5)=''
as

declare
	@sql varchar(max)=''

select 
	@sql='
SELECT TOP 10 * FROM dbo.['+@table_prefix+'$Sales Invoice Header]'
	
print @sql	/*	-- when you want to execute just comment 
EXEC(@sql)	--*/

GO

Open in new window

After you created the stored procedure you can execute it with:
exec dbo.spDynamicCompanyQuery 'EN'

exec dbo.spDynamicCompanyQuery 'FR'

Open in new window

0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> Is it possible to use a variable as part of a table name? <<

No, not in SQL Server, as was strongly implied by the previous answers.

IF this report is only ever run 1-up, i.e. two people/tasks NEVER run the report at the same time, you could also use a synonym to access the correct table.  [If necessary, I guess you could even exclusively lock the table while a SHORT report was running.]

This should probably be considered a "quick and dirty work-around" rather than a final resolution, because of the stated restrictions on usage.

Change the code to use a synonym, such as:
dbo.[Co$Sales Invoice Header]
in place of the actual table name to be reported on.

Then, just prior to running the report, set the synonym to match the particular table you need:

--DECLARE @co char(2) --must contain the needed 2-char country code

DECLARE @sql nvarchar(1000)
SET @sql = 'if exists(select 1 from sys.objects where type = ''SN'' and name = ''Co$Sales Invoice Header'') drop synonym dbo.[Co$Sales Invoice Header] create synonym dbo.[Co$Sales Invoice Header] for dbo.[' + @co + '$Sales Invoice Header]'
EXEC(@sql)

-- all the actual reporting code uses the static synonym, and so does not have to be dynamic SQL
SELECT
FROM dbo.[Co$Sales Invoice Header]
WHERE ...

...
0
 
ZberteocCommented:
When you assign points you don't have to close the question.
0
 
Paul GAuthor Commented:
Simplest for my needs
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the accept, but it appears that you have been given some pretty good answers, with no follow-on questions on your part.  

So .. why the C grade?  

Reason I ask is because C grades usually require an explanation, and experts tend to remember and possibly avoid askers that give C grades.
0
 
Paul GAuthor Commented:
I do appreciate your efforts but I was looking for something very simple.
I thought the C grade was for my 'solution' as from a technical standpoint it doesn't do what I asked for.

Apologies, I did not intend to cause offence
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>it doesn't do what I asked for.
Okay, but 'you can't do that' is a valid solution when it is the correct one, so even though you wish a simple solution, it's not less of an answer when experts tell you the complex answer because simple is not available.

For example, 'How to paint my house in one hour' is not likely possible, so an answer of 'You can't do that' does not make for a C-grade answer.
0
 
ZberteocCommented:
The solution it is actually the only way possible and your appreciation of being "simple" or "complicated" is a matter of opinion based on your own experience, which in this matter is obviously lacking,  and should not have anything to do with the quality of the answer.

So if is the only way and also a working solution there is no reason to give a grade of C. I think you also probably are not aware of the EE rules, but normally the common sense should be enough.
0
 
Paul GAuthor Commented:
It seems that you are not capable of accepting a sincere apology.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.