Solved

Use variable as part of tablename

Posted on 2013-10-25
13
478 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
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 200 total points
Comment Utility
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:ScottPletcher
ScottPletcher earned 200 total points
Comment Utility
>> 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
Comment Utility
Thanks you all for your input. I thought there may be a simple solution.
Find and Replace seems the way to go.

Thanks.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

Expert Comment

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

Author Closing Comment

by:kcoxon
Comment Utility
Simplest for my needs
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
It seems that you are not capable of accepting a sincere apology.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Select Prior Ship Date Data 2 28
SQL server 2008 SP4 29 31
while loop in html mail format 5 32
t-sql splitting string column 5 24
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now