?
Solved

Use variable as part of tablename

Posted on 2013-10-25
13
Medium Priority
?
493 Views
Last Modified: 2016-02-11
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.
0
Comment
Question by:kcoxon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 39600689
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
 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 39600843
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
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 400 total points
ID: 39601644
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 400 total points
ID: 39601660
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 39607025
>> 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
 

Accepted Solution

by:
kcoxon earned 0 total points
ID: 39612353
Thanks you all for your input. I thought there may be a simple solution.
Find and Replace seems the way to go.

Thanks.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39612428
When you assign points you don't have to close the question.
0
 

Author Closing Comment

by:kcoxon
ID: 39621086
Simplest for my needs
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39621407
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
 

Author Comment

by:kcoxon
ID: 39621790
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39621800
>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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39621985
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
 

Author Comment

by:kcoxon
ID: 39647188
It seems that you are not capable of accepting a sincere apology.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question