Link to home
Start Free TrialLog in
Avatar of mimil
mimil

asked on

Select from table referenced in a record

Hello,

Is it possible to select records from a table when all we have is the table name as string from another select?

Example:
Table1 contains
id    table_name
21   a_name_of_table

I would then like to select records from a_name_of_table knowing that the table id is 21 (But not knowing the table name).

Thanks for the tips
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mimil
mimil

ASKER

Thanks acperkins,

What would be the reason for not doing it?
The short answer is that it requires Dynamic SQL.  This has performance and security problems associated with it.  I am sure you have heard about SQL injection.  The other problem IMHO is that it could very quickly turn into a maintenance nightmare.

Just my 2 cents worth.
"Just my 2 cents worth."


Your 2cents always seem to be on the mark :)
Avatar of mimil

ASKER

Thanks again acperkins,

I have just read a few article on SQL injection and it was well worth the reading. I am not very concerned as the entire sql statment should be generated from database data rather than user input but I could very well be wrong......

If better solution are available, why not. I am building a reconciliation DB which will match n systems data. I would like to have the various reconciliation pairs definition in a table. From this table and a recon_compo table defining which reconciliation pairs are part of a greater recon, I would like to arrange all the data into a consolidated temp table to be exported to excel using DTS. The issue is that the fields reconciled for a recon pair may vary thus pushing for individual tables to hold each recon exceptions (This is were I am not sure how to build a single table to hold generic data ......).

The initial question was asked to know if I could dynamically from the data in the reconciliation pairs definition in a table build the statement to query a specific recon exception table (Which definition will change depending on the type of recon done).

Hopefully it is clear. Whatever the outcome of this post, acperkins already got the points.
Cheers.
Don't get me wrong, I understand what you are trying to achieve.  I have been down that road already, it is just fraught with problems (aside from the dynamic SQL side of it).  If you think you can keep the names of the tables in sync, more power to you.  And trust me I am not being facetious.  I could not do it.
P.S. Before awarding any points, make sure you test the code.  It was written off the cuff and totally untested.
Avatar of mimil

ASKER

Let's try it out and see how it goes ... If it becomes a mess, we'll flatten all the structure and code the logic in SPs ....

Thanks for the tips and the friendly tone.
BTW: The code works as it is.