Dealing with non standard column names...crazy!

[RSBLaird]

Not sure quite how to even approach this question so feel free to get clarification if I thoroughly confuse...>God knows I AM!

Ok...
I have a procedure that pulls three columns from any given table. (Variable @TableName) But some of the tables do NOT have the same column name. Example...some tables have Code, Description, Name...OTHER tables have Abbreviation, Description and Name, and still OTHERS have Abbreviation & Description but no Name column. You can only imagine the other possibilities. but you get the idea...

So I've figured out how to make those work with dynamic SQL and temp tables...works like a charm! BUT, now I've been asked to add one more field, that again is not in every table. But if it doesn't exist, just show a default value. *sigh*
Is there a way to join to a "key field" if you do not know the name ahead of time? And if not is there a way to get the column name based on the ordinal_position? And can you get it from a temp table? (Global or local?)

If you understand any of this and can help, please respond...thank you in advance for any help you guys can provide.


Thanks,
RSBLaird

 

[markros]

Well, to get all field for the table, look at INFORMATION_SCHEMA.Columns

 

[gmmastros]

If you go with the view suggestion I made the other day, you wouldn't have this problem.

Ex:

Create View rpt_Table1
As
Select Code, Description, Name From Table1

Create View rpt_Table2
As
Select Code, 'Hard Code Data' As Description, Name From Table2

Create View rpt_Table3
AS
Select Appreviation as Code, Description, Name From Table3

Then, change your report to use the views instead of the tables. You can ensure that each view has exactly the same columns so that the report becomes easy to write. As you can see with the 2nd example, you can even hard code data for certain columns.

This really will make things a lot simpler (although it means a lot of set up work initially).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RSBLaird]

As I mentioned "the other day", I do not know the table name ahead of time, it is a variable. How can you write a view on a variable? I didn't think it was possible.

The @TableName is a table that varies with the selection in the report. So when you click any given table it's supposed to give you the same three (now four) fields every time, even though not all those tables have all four fields.
And what's worse there are 675+ tables to do this with...I cannot write a CASE statement, or IF statement for each of those tables...wouldn't want to...not practical...and too much maintenance. I have already done somewhat of what you are suggesting in some dynamic SQL and temp tables...to give a stable name to each of the fields...but one can only take that so far. Too many variations to accomodate.

So unless I am completely missing the point of your "view" suggestion, I'm afraid it's just not what I'm looking for.
Thank you any way.

Any other ideas?

~RSBLaird

 

[r937]

what if we told you there was no way to write your general procedure to return 2 or 3 or 4 columns from any one of 675 tables without knowing the column names

what would you do then?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[RSBLaird]

I would cry a little, then try again and FIND a way!

 

[JoeAtWork]

All the different suggestions you've been given in your two threads come down to the same hard reality, in that somewhere you have to specify the mapping between the table names and column names. You could do that with:
1. Views to all tables with aliases for column names
2. Create a table to look up columns based on Table name
3. Create a massive CASE statement in your stored procedure

...and many other ways.

Imagine you were trying to give these instructions to a human being "You need to select four columns based on which Table name I give you, but I won't tell you which columns to choose from which table" The human being is going to reply "How can I do that, I'm not psychic!". Well, you cannot make your SQL Server a psychic either - you have to tell it explicitly what it's supposed to do.

It's going to be a lot of up front set up time no matter what you do, you just can't get around that, you have 675x4 individual pieces of information that must be stored somewhere.

 

[RSBLaird]

Thank you Joe...guess I just needed it spelled out for me...and I must admit, my first reaction to my bosses request was just what you've said...but I am stubborn and was determined to find a way. But I am seeing there's just no way around it. *sigh*

But I've learned something new, and will have to do the set up, but we will be able to accomplish it, just with a bit longer time table.

Thanks to all who tried to steer me right...guess I'm finally defeated. LOL

Sincerely,
RSBLaird

 

[SQLSister]

My personal take on this is that if you are designing assuming you are going to be able to vary the tables based on what you want at the moment, you need to hire a database specialist to redesign your database and write code that will perform well. This is the worst possible way to access database inmformation.

Suggest you read and reread and reread point 9 in this link

http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

"NOTHING is more important in a database than integrity." ESquared

 

[JoeAtWork]

SQLSister - that was my initial reaction too, but if you read his other thread it turns out the idea is not as mad as it sounds.

Basically, he wants one stored procedure that could retrieve all rows from any of his lookup tables. So whether he wants a list of all possible Colors, Countries, Products, Vendors, etc., he just passes in the table name and retrieves whichever list.

I guess the only way this could be accomplished easily is before the database is built, you make a requirement that all such tables have ID, Name and Description fields, etc.

 

[RSBLaird]

Actually I'm a "she"...LOL
But yes, SQLSister & Joe...good points made by both of you...and beleive me this whole idea was not mine...I am a contractor...and the client asked for this and believed that MOST of the tables in their database had some sort of standard, (Abbreviation, Name, Description)...thanks to my "research" into all this he's discovered that is indeed NOT the case. I did my best to get around it, and was able to up to a certain point, but then he brought in the additional field that became a major fly in the ointment, hence the two threads.
We have been discussing it, and it looks like he is going to have someone on his SQL team get a plan going to get that standard in place..hard to do once your database is established...but we'll see where they go from here. And in the mean time, it looks like I will have to create views for a good chunk of these tables...luckily it's only the "Code Lookup" tables, so it could be worse.

Thanks again for your help, I have definitely learned something, and will not ever have to ask a question like this again.

Take care,
Brenda (RSBLaird)

 

[RSBLaird]

And just to let you see what I DID do to try and get around this...here is the code for the procedure I originally created. Honestly it works great...mainly because the fields we were going for were in alphebetical order (Abbreviation, Code, Description, Name...and none of the tables had only Code & Name or Abbreviation & Name, so it became possible to do it this way. (see code below) But once he threw in the other field he wanted me to look for, it threw that whole dynamic off, hence becoming impossible again. *sigh*
Anyway...it may be ugly, it may be round about, but it kept me from having to do views of all the tables invovled and works like a charm...just again I warn you, NOT pretty, and probably not the most efficient. But the best way I could think of on such short notice, and this thing runs in a split second every time. So I'm happy with it.

ALTER PROCEDURE [dbo].[MDSP_LookUpTableCols]
@TableName varchar(200) 
AS 

--Declare variables
DECLARE @FinalResults varchar(4000)
DECLARE @ColumnNames varchar(1000)
DECLARE @numbercolumnfound int
DECLARE @Abbrv_hldr varchar(16)

--Create a table with desired column names to return to the report
CREATE TABLE #Final_results (
[ABBRV]		varchar(16),
[DESC]		varchar(2000),
[NAME]		varchar(300),
)

--get column names for particualr table
SELECT name
INTO #LKUP_TableData
FROM sys.columns 
WHERE [object_id] = OBJECT_ID(@TableName)
AND Name in ('Code','Abbreviation','Name','Description');
					
Set @numbercolumnfound = (SELECT count(*) from #LKUP_TableData)

IF @numbercolumnfound = 3
 BEGIN
	--Set the column names into a comma separated list
	Set @ColumnNames = (SELECT DISTINCT	ColumnName = 
			substring((SELECT DISTINCT ( ', ' + [Name] )
						FROM #LKUP_TableData 
						FOR XML PATH( '' )),3,1000))

	/*utilize comma separated column list in dynamic SQL 
	Statement to get variable table name 
	and columns from that table*/
	Set @FinalResults = 'select '+ @ColumnNames +' from ' + @TableName 

/*Execute the dynamic sql to insert the data into the 
	table to return it to the report*/
		INSERT INTO #Final_results
		EXEC(@FinalResults)

		
 END

IF @numbercolumnfound = 2
 BEGIN
	--Set the column names into a comma separated list
	Set @ColumnNames = (SELECT DISTINCT	ColumnName = 
						substring((SELECT DISTINCT ( ', ' + [Name] )
						FROM #LKUP_TableData 
						FOR XML PATH( '' )),3,1000))
	IF @ColumnNames = 'Description, Name'
	BEGIN
		/*utilize comma separated column list in dynamic SQL 
		Statement to get variable table name 
		and columns from that table*/
	 Set @FinalResults = 'select Abbrv = '+'''No ABBRV'''+', '+ @ColumnNames +' from ' + @TableName 

		/*Execute the dynamic sql to insert the data into
		 the table to return it to the report*/
	 INSERT INTO #Final_results
	 EXEC(@FinalResults)
	END
	IF @ColumnNames = 'Code, Description'
	 BEGIN
		/*utilize comma separated column list in dynamic SQL 
		Statement to get variable table name 
		and columns from that table*/
		Set @FinalResults = 'select '+ @ColumnNames +', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName 

		/*Execute the dynamic sql to insert the data into
		 the table to return it to the report*/
		INSERT INTO #Final_results
		EXEC(@FinalResults)
	 END
	IF @ColumnNames = 'Abbreviation, Description'
	 BEGIN
	   /*utilize comma separated column list in dynamic SQL 
		Statement to get variable table name 
		and columns from that table*/
		Set @FinalResults = 'select '+ @ColumnNames +', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName 

		/*Execute the dynamic sql to insert the data into
		 the table to return it to the report*/
		INSERT INTO #Final_results
		EXEC(@FinalResults)
	 END
 END

IF @numbercolumnfound = 1
 BEGIN
	--Set the column names into a comma separated list
	Set @ColumnNames = (SELECT DISTINCT	ColumnName = substring((SELECT DISTINCT ( ', ' + [Name] )
										FROM #LKUP_TableData 
										FOR XML PATH( '' )),3,1000))
IF @ColumnNames = 'Abbreviation'
	BEGIN
		/*utilize comma separated column list in dynamic SQL 
		Statement to get variable table name 
		and columns from that table*/		
		Set @FinalResults = 'select '+ @ColumnNames +', '+'Description = '+'''Description does not exist in this table'''+', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName 

		/*Execute the dynamic sql to insert the data into
		 the table to return it to the report*/
		INSERT INTO #Final_results
		EXEC(@FinalResults)
	END

IF @ColumnNames = 'Code'
	BEGIN
		/*utilize comma separated column list in dynamic SQL 
		Statement to get variable table name 
		and columns from that table*/
		Set @FinalResults = 'select '+ @ColumnNames +', '+'Description = '+'''Description does not exist in this table'''+', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName 

		/*Execute the dynamic sql to insert the data into
		 the table to return it to the report*/
		INSERT INTO #Final_results
		EXEC(@FinalResults)
	END

IF @ColumnNames = 'Description'
	BEGIN
		/*utilize comma separated column list in dynamic SQL 
		Statement to get variable table name 
		and columns from that table*/
		Set @FinalResults = 'select Abbrv = '+'''No ABBRV'''+', '+ @ColumnNames +', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName 

		/*Execute the dynamic sql to insert the data into
		 the table to return it to the report*/
		INSERT INTO #Final_results
		EXEC(@FinalResults)
	END
IF @ColumnNames = 'Name'
	BEGIN
		/*utilize comma separated column list in dynamic SQL 
		Statement to get variable table name 
		and columns from that table*/
		Set @FinalResults = 'select Abbrv = '+'''No ABBRV'''+', '+'Description = '+'''Description does not exist in this table'''+', '+ @ColumnNames +' from ' + @TableName 

		/*Execute the dynamic sql to insert the data into
		 the table to return it to the report*/
		INSERT INTO #Final_results
		EXEC(@FinalResults)
	END


END

IF  @numbercolumnfound < 1
			/*If no fields exist in the given table, create placeholders
			for each of the desired fields and a default value for each.*/
	BEGIN
		INSERT INTO #Final_results
		SELECT [ABBRV] = 'No ABBRV',[DESC] = 'Description does not exist in this table', [NAME] = 'Name does not exist in this table'
	END

--return results to report
SELECT *,PKTableName=@TableName FROM #Final_Results

Thanks again guys & girls ;-)...
RSBLaird

 

[JoeAtWork]

In my opinion, creating the views is the solution that your client's dba should adopt - far less hazardous then renaming the actual field names in the tables (that current stored procedures, triggers, undocumented code, etc., may rely upon).

 

[RSBLaird]

I would tend to agree with you, and I've given my recommendation as much...but we will see what they are going to.
So how'd ya like that mess of a procedure above^^. LOL

Thanks again!
RSBLaird