Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Use variable as part of tablename

Posted on 2013-10-25
13
Medium Priority
?
514 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:Paul G
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 70

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:
Paul G 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:Paul G
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:Paul G
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:Paul G
ID: 39647188
It seems that you are not capable of accepting a sincere apology.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

571 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