Trying to return a table of tablenames dynamically

Question

Trying to return a table of tablenames dynamically

Viewing 5 posts - 16 through 19 (of 19 total)

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

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

mar.ko (9/14/2015)
Mine was one line of code to describe the list and one line of code to convert it to a table via the function call !

How are you going to modify your list in future?

Sean's list is in a permanent database table. Easy enough to add, or remove, a single row from that. Easy enough to JOIN that table to a list of valid table names - e.g. to have a constraint that only allows you to have valid table names in the list. And a Foreign Key so that if someone tries to drop a Table, and it is used in a List somewhere, then the system will catch that and prevent it happening.

Lots of benefits from having the data row-by-row in a table, rather than in a delimited-list

If your data starts out in a delimited list then use your splitter function to split it and insert it into the table as a starter data set, like this:

create table GroupTables

(

GroupID int not null,

TableName sysname not null

)

INSERT INTO GroupTables

SELECT 1 as GroupID,

YourSplitterColumnName AS TableName

FROMdbo.UtilTableFromString(@TABLES1)

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

mar.ko (9/14/2015)
However, I don't agree that using comma-delimited lists violates any rules of relation theory.
A list is a list....whether it's in table form or a string.

BWAHAA!!! No offense but that is rubbish. This absolutely violates 1NF. A table intersection should contain one and only one value.

Here is the quote from Wikipedia (https://en.wikipedia.org/wiki/First_normal_form) "Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else)".

Also note how much more code is involved in building the list with standard SQL.
Mine was one line of code to describe the list and one line of code to convert it to a table via the function call !

Actually it isn't more code. In fact it is probably less code. Consider all the code you posted, now add in the splitter function. That is a lot of code to get a list of table names. Here would be the entirety of the code using a table approach.

Create procedure TablesForGroup

(

GroupID int

) as

set nocount on;

select TableName

from GroupTables

where GroupID = @GroupID;

Another consideration is updating the list. How challenging it is for you to update your denormalized column?

You should not use the amount of code required to make a design decision. You should make design decisions based on how efficient it is for the system. Convenience to the developer is irrelevant.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mar.ko Default port Points: 1451 More actions · Answer 3

Thanks Sean, but remember, my potential solution would effectively be 2 lines of code.

One to define the table via string, the other to call a function to convert it to a table.

mar.ko Default port Points: 1451 More actions · Answer 4

Awesome Kristen.....and thanks....

I'll remember this one for sure.

Very elegant. I was all bollexed up with how to specify the CASE statement.

Now I Just execute: SELECT * from dbo.FnReturnTablesForGroup(1)

And a list of tables appears for group #1.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

mar.ko (9/14/2015)
Thanks Sean, but remember, my potential solution would effectively be 2 lines of code.
One to define the table via string, the other to call a function to convert it to a table.

Again the number of lines of code is not an indicator of the quality of the solution. I would also argue that yours is not 2 lines of code because it has to use the function. Those lines of code would also be in your solution. If you use a table then it is nothing more than a single select statement. When all is said and done this is your system so you can decided which approach to take but storing delimited data is going to cause you a lot of headaches. Hopefully you have enough information to figure out your approach and I hope it works well for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/