Using a row from anoher table in the FROM clause

Question

Post reply

Using a row from anoher table in the FROM clause

R Loberg

SSC Enthusiast

Points: 100
More actions
October 12, 2007 at 3:08 pm

#76796

I have a vendor database where they are holding table names in other tables, so I'm trying to come up with a query (that I can use in a view) that will use the table name returned from a query to another table.
Ex: TABLEA has a column TABLENAME that contains the name of a table I want to query. The value of TABLENAME is TABLEB may change over time to TABLEC or TABLED.
So, I want to query TABLEB, but it's name may change. The statement SELECT * FROM (SELECT TABLENAME FROM TABLEA) does not work because it does not use the returned record as the table name in the FROM clause. Instead it sees the subquery as an inline table and errs.
Any ideas?
Thanks!!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

You could use dynamic sql, i.e. construct the query string and then use execute, like:

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT tID,... FROM '+

@table +'.. rest of the query'

EXECUTE(@query)

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

Just a little modification to Andras suggestion. Use the QUOTENAME function with the @table variable. This will put brackets ([]) around the table a prevent problems with table names containing spaces etc.

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT tID,... FROM '+

QUOTENAME(@table) +'.. rest of the query'

EXECUTE(@query)

Bob Fazio SSChampion Points: 10794 More actions · Answer 3

The OP was to add the table name to a view. What was asked is NOT possible. However, the dynamic SQL is, and an alternate of it would be to possibly create a trigger on the table that would dynamically alter the view on update. (Make sure your permissions are tight). I'll try and put something together in a bit to see if it is possible.

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

Yes, this works. 🙂 Have fun.

You will need to create the view first, but I am sure you could even code that in too.

CREATE TABLE [view_config](

[view_name] [sysname] NOT NULL,

[table_name] [sysname] NOT NULL

) ON [PRIMARY]

CREATE TRIGGER [trig_ViewAlter]

ON [view_config]

AFTER update

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

DECLARE @mySQL NVARCHAR(200)

SELECT @mySQL = 'ALTER VIEW ' + view_name + ' AS SELECT * FROM [' + table_name + ']'

FROM inserted AS i

EXEC sp_executesql @mySQL

END

You might be able to do this as a function also, but I don't have time to play.

R Loberg SSC Enthusiast Points: 100 More actions · Answer 5

Thanks for everyone's help!! After my testing last week, I was pretty sure I'd need an sp or a user-defined function to do this. I will start my next round of testing.

Thanks again!!

R