May 26, 2011 at 6:41 pm
I am trying to write a stored procedure that joins a table or does unions that has the table name in another table:
ie, CREATE TABLE tables
(
@table varchar,
@foreignKey varchar
@data varchar
)
From here I would want to do something like:
SELECT *
FROM table1
union
SELECT *
FROM table2
union *
SELECT *
FROM table3
where the table1, table2 and table3 come from the tables table.
Thanks,
Tom
May 26, 2011 at 8:04 pm
tshad (5/26/2011)
I am trying to write a stored procedure that joins a table or does unions that has the table name in another table:ie, CREATE TABLE tables
(
@table varchar,
@foreignKey varchar
@data varchar
)
From here I would want to do something like:
SELECT *
FROM table1
union
SELECT *
FROM table2
union *
SELECT *
FROM table3
where the table1, table2 and table3 come from the tables table.
Thanks,
Tom
Red flags everywhere :exclamation:
Before we formally decide to make a design change...what are you trying to do?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 26, 2011 at 11:15 pm
What I am trying to do is try to return the values in certain reference tables. I don't know what the name of the tables are - this is dynamic. The "Tables" table have a list of reference tables in them that I can use for dropdowns in my application. The record would have the 4 columns, PK, Table name, name of the PK in the table, and the name of the column that has the text that we want to show in the dropdown.
So what I want to do is call a stored procedure that will send back the records from all the tables that are in the "Tables" table.
I can do it doing doing a select something like:
********************************************
DECLARE @sql varchar(8000)
SELECT @sql = ' SELECT ''' + ForeignKeyTable + ''' AS TableName, '
+ ForeignKeyField + ' AS Value , '
+ ForeignKeyValueField + ' AS Text'
+ ' FROM ' + ForeignKeyTable
+ ' Order By TableName, Value'
FROM Tables
SELECT @sql
EXEC (@sql)
*************************************************
I was trying to see if there was a way to do this without using cursors or dynamic SQL.
Thanks,
Tom
May 27, 2011 at 9:51 am
tshad (5/26/2011)
What I am trying to do is try to return the values in certain reference tables. I don't know what the name of the tables are - this is dynamic. The "Tables" table have a list of reference tables in them that I can use for dropdowns in my application. The record would have the 4 columns, PK, Table name, name of the PK in the table, and the name of the column that has the text that we want to show in the dropdown.So what I want to do is call a stored procedure that will send back the records from all the tables that are in the "Tables" table.
I can do it doing doing a select something like:
********************************************
DECLARE @sql varchar(8000)
SELECT @sql = ' SELECT ''' + ForeignKeyTable + ''' AS TableName, '
+ ForeignKeyField + ' AS Value , '
+ ForeignKeyValueField + ' AS Text'
+ ' FROM ' + ForeignKeyTable
+ ' Order By TableName, Value'
FROM Tables
SELECT @sql
EXEC (@sql)
*************************************************
I was trying to see if there was a way to do this without using cursors or dynamic SQL.
Thanks,
Tom
You cannot do it without dynamic SQL. SQL is a declarative language. Things like this do not work:
DECLARE @table_name SYSNAME = N'dbo.some_table' ;
SELECT *
FROM @table_name ;
Dynamic T-SQL allows us to bend the rules of SQL by letting us simulate an interpreted environment. As you have have seen something like this can work:
DECLARE @sql NVARCHAR(MAX) ;
DECLARE @table_name SYSNAME = N'dbo.some_table' ;
SET @sql = N'SELECT *
FROM ' + @table_name + ';' ;
EXEC (@sql) ;
All I'll say about the path you're going down is "don't go". Choose a proper database design.
<mini-rant>
If you go down the dynamic sql path you'll end up with code that generates code all over the place...which is a nightmare to develop, debug, secure, tune. The list of pitfalls is long.
If you're looking to make things dynamic and avoid dynamic sql you may arrive at "common lookup tables" and think they are a better idea. I'm not a fan of these either because they too have tons of long term pitfalls. If you go down this path you may be able to avoid dynamic sql but you will have to become very familiar with cross-tab, PIVOT and UNPIVOT queries to simulate tabular structures and performance will tank. You will also lose any chance of type-safety and proper referential integrity as well, among other things. Here is a balanced review of "common lookup tables"...please at least read the conclusion if you don't care to read the whole article: http://www.projectdmx.com/dbdesign/lookup.aspx
The motivation for what you're doing is reducing database maintenance...but it is a trap. It never pays off if you go down the dynamic sql or common lookup table road.
</mini-rant>
The Proper Solution: create CRUD procs for each of your reference tables that handle data for that table and call the correct proc from your application for what you're doing. IMHO storing metadata about your reference tables so you can build SQL statements on the fly is a poor way to do this.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 27, 2011 at 10:28 am
As you can see, I had come to the same conclusion you mention - dynamic SQL. I was just curious if there was a different way to handle it.
I also agree with all you said about design of these lookup tables and that is how they are set up - each with its own class, crud logic and various methods to access the tables for the programmers.
What I was doing was creating a method to allow the clients to add attributes to user table for themselves. This table allows our maintenance program to build dropdowns on the user page based on the tables in this common table. Whatever is in the table gets added to the user create and edit screen as dropdowns for the user to pick.
My maintenance program will then read these records and created dropdowns that have all the records from each table in its own dropdown. If there are 4 records in the table, there will be 4 dropdowns - each with all the records its table has in it.
I like the article and agree that a common table as mentioned is a nightmare. I just finished a project where the database was set up that way. Not just the look up tables but regular tables. The way you knew which table to deal with was to use a type field in the "where" and "join clauses. Caused all kinds of issues.
While there were less tables to deal with you had to have many general columns such as svalue1, svalue2 etc. that had different kinds of data based on the table type you were dealing with.
Thanks,
Tom
May 27, 2011 at 10:37 am
I was just curious if there was a different way to handle it.
Yes, there is:
Build a module/screen/class/whatever-its-called-in-the-middle-tier-or-front-end-language of your maintenance application that applies to each reference table to allow users to manage data in that table and call the CRUD procs to manage data...instead of trying to build one module that calls a mess of dynamic SQL code in the data layer.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply