Solved

Use variable as part of tablename

Posted on 2013-10-25
13
489 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 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 26

Assisted Solution

by:Zberteoc
Zberteoc earned 200 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 200 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 200 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 26

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 65

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 65

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 26

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

733 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