Using a variable as a table name in TVF or Stored Proc

Transact-SQL
1

I'm on a database where they have setup the countries stock tables as exactly the same except the table name is a different e.g.

Select * from [America$StockTable];
Select * from [Europe$StockTable];
Select * from [Asia$StockTable];

  1. Is it possible to pass the table name into a table variable function?
    i.e. so it does this.

Set @CompanyTable = 'Europe'
Select * from [+ @CompanyTable + $StockTable];

  1. Is it possible to pass the table name into a stored procedure without using Dynamics SQL?

Thanks,
Rog.

CREATE TABLE [dbo].[America$StockTable]
( [Posting Date] [datetime] NULL,
[InventoryLocation] varchar NULL,
[Qty] [decimal](38, 20) NULL
)
;
CREATE TABLE [dbo].[Europe$StockTable]
( [Posting Date] [datetime] NULL,
[InventoryLocation] varchar NULL,
[Qty] [decimal](38, 20) NULL
)
;
CREATE TABLE [dbo].[Asia$StockTable]
( [Posting Date] [datetime] NULL,
[InventoryLocation] varchar NULL,
[Qty] [decimal](38, 20) NULL
)
;

2

No. Dynamic SQL is the only way to handle that. (Or a CASE statement that returns values from Table1, Table2 etc. depending on the value of an @Parameter)

Could you use a VIEW?

CREATE VIEW dbo.MyViewname
AS
(
    SELECT 'America' AS SourceTable,
           [Posting Date], InventoryLocation, Qty
    FROM [dbo].[America$StockTable]
    UNION ALL
    SELECT 'Europe' AS SourceTable,
           [Posting Date], InventoryLocation, Qty
    FROM [dbo].[Europe$StockTable]
    UNION ALL
    SELECT 'Asia' AS SourceTable,
           [Posting Date], InventoryLocation, Qty
    FROM [dbo].[Asia$StockTable]

and then

SELECT [Posting Date], InventoryLocation, Qty
FROM dbo.MyViewname
WHERE SourceTable = @MyParameter
      AND InventoryLocation = @XXX etc
3

Thanks Kristen,

I guess I could use a view, the tables have about 20million rows in each and if I wanted to select stock for one day, I'm just wondering how SQL executes this as I'm not sure:

if I do on the view:

SELECT [Posting Date], InventoryLocation, Qty
FROM dbo.MyViewname
WHERE SourceTable = @MyParameter
AND InventoryLocation = @XXX
AND [Posting Date] = '01/11/15'

Does the sql select everything from the table first in the view then get the date data after?

So it is different to selecting the data direct from table only for the date needed, if that makes sense.

Sorry for my lack of knowledge here, just want to make sure I'm not selecting the whole table into memory each time.

4

The view will be a performance issue.

But the real coding and performance issue is the overall design. You should have a single table with the first column of the clustered index being an encoded tinyint column, with, for example, 1=America, 2=Europe or 3=Asia.

Then just include the correct condition in the WHERE clause. That will make for very efficient lookups while eliminate the complexity of different tables.

1 Like
5

You could put an index on the VIEW, which would help with the performance issue - assuming that you can;t improve the design as Scott suggests.

Can you create a suitable table and put a Trigger on the existing 3x tables so that your (i.e. effectively read-only) composite table is kept synchronised, but then performs well for queries?

6

Thanks Scott.

Yep I agree the current setup is a real pain in the backside. I think I'm going to have to create the script three times hardcoded with each company name and then call which ever script is needed, problem will be in the future if they create more companies someone will have to script out one of the existing companies code and create another script for the new company.

Thanks Scott and Kristen for your help.

7

Or:

Merge the data from the three tables into one, composite, table

Create VIEWs in place of the original tables

Put INSTEAD OF trigger on the VIEWs which will store any changes in the underlying, composite, table.

For any, new, queries use the New Table

8 
USE MyDb
GO

IF OBJECT_ID (N'dbo.tvfStockTable') IS NOT NULL
   DROP FUNCTION dbo.tvfStockTable
GO

CREATE FUNCTION dbo.tvfStockTable(@Area varchar(20))
RETURNS @StockTable TABLE 
(
	[Posting Date] [datetime] NULL,
	[InventoryLocation] [varchar](10) NULL,
	[Qty] [decimal](38, 20) NULL

)
AS

BEGIN
   
   IF @Area = 'America'
      INSERT @StockTable
      SELECT [Posting Date], [InventoryLocation],[Qty]
      FROM [dbo].[America$StockTable]; 
   IF @Area = 'Europe'
      INSERT @StockTable
      SELECT [Posting Date], [InventoryLocation],[Qty]
      FROM [dbo].[Europe$StockTable];
   IF @Area ='Asia'
      INSERT @StockTable
      SELECT [Posting Date], [InventoryLocation],[Qty]
      FROM [dbo].[Asia$StockTable];
   RETURN
END
GO
9

Thanks Joseph S Torre that looks interesting, will give it a go.

10

Fantastic joterre that works perfectly.

11

Just checking something on google and I may end up searching in vain but wondered if you know if the bottom is possible in t-sql before I started hunting around, thanks.

A Correlated Subquery in the From section?

select a.[], b.[]

from table1 as a

left outer join (select c.[] from table2 as c
where a.[] = c.[]
) as b
on a.[] = b.[]

12

I'm interested to know how it performs (compared to the VIEW I proposed). TVF usually perform quite well (unlike Scalar functions :frowning: ) but I think the optimiser might have a better shot at the VIEW. No substitute for a decent TEST though :slight_smile:

The JOIN is fine but the test aganist A. (i.e. anything "outside" the query) will have to be in the ON and not inside the query.

You might find that a CTE or an OUTER APPLY fits the bill better.

13

@rogerclerkwell1,
Do some experimentation. A correlated subquery runs once for each row in the query producing RBAR. That's bad medicine!
A subquery joined externally, as Kristen pointed out, join outside the subquery in the on clause, it will avoid unnecessary nested looping.