June 9, 2014 at 8:03 am
I hope I can explain this so that it makes sense.
In a database, which is the back-end to a web application, I want to have UNION query which returns values from four or five columns in many different tables, all storing potentially quite diverse data. The criteria for which records to retrieve from each table (i.e. what goes in the WHERE clause) could vary a lot from table to table. Also, I want it to be as simple as possible to add additional tables to the list of those included in the UNION query, and for end-users of the application to be able to choose which tables are included.
My idea is to have a single table which would store the 'settings' for how each of the tables fitted into the UNION query. So this table would contain a record for each table which could have its contents included in the UNION query. Each record would contain information about the criteria for extracting data from that table, perhaps in the form of the name of a stored procedure which would be unique to that table and would do the initial SELECT query. Each record would also include an 'Enabled' column with a Bit datatype.
The idea is that a central stored procedure would then iterate through this table, and for each record in the table where the 'Enabled' column was true, it would execute code to get the data from the table specified in that record according to the criteria specified in that record (perhaps just by executing the stored procedure whose name was in that record), and then the results of all of those code blocks would be amalgamated into the final UNION query.
I realise that, if you even understand what I'm trying to describe, this sounds very complicated. It sounds to me as if it would have to use dynamic T-SQL, which I know is a bad idea. And it sounds like it would perform horribly. So, as well as asking how I might actually achieve what I've described (my biggest issue is knowing how to store the names of procedures in a table and then use these in another procedure to actually execute the named procedures), I'd also be interested to hear if anybody had suggestions of better ways I could accomplish this! For those who like a challenge, I'll describe a scenario below analogous to the one I'm facing which might help set out the problem better.
==============================================
Example:
Imagine I have a giant refrigerator. In it are all different types of product. For example, there are products of type 'Milk', 'Eggs', 'Meat', etc. Now, let's say that I have a database table for each product type, so a table called Milk, a table called Eggs, a table called Meat, etc. Each table contains many records for all the different items of that type in my fridge.
What I want to do is create a single procedure which will return a list of all the items in my fridge that need to be thrown out, along with the date when they are to be disposed of. I will achieve this by creating a UNION query that selects records from each of my product type tables, and returns columns 'Product Name', 'Reason For Disposal' and 'Dispose By Date'. The catch is that rather than storing the date against each item in each table on which it should be disposed, I want this date to be calculated, and I want to calculate it differently depending on the product type.
For example, I might want to select any items from the Milk table where the value in the 'IsLonglife' column is False and the 'PurchaseDate' has a value greater than a week ago. I might want to select items from my Meat table where the value in the 'IsCooked' column is True and the 'PurchaseDate' column has a value greater than four days. And so on, with the ability for an administrator of the web app to specify that they don't want certain product types to be included in the list (e.g. "Don't include reminders about vegetables").
So the plan would be to somehow have a table which stored these settings. In it would be a record for each product type (milk, meat, etc.), specifying where to find the records relating to that product type (ie. the table name), what the criteria for including items from that product type are (e.g. "WHERE [IsLonglife] = 0 AND [PurchaseDate] < DATEDIFF(dd, -7, GETDATE()) ) and what the disposal date for that item should be. And then there would be a single procedure which was able to take the information from this settings table and use it to extract the individual item records for each product type from their respective tables and present them as a single list.
Does that make sense? Anyone feeling creative and smart?
June 9, 2014 at 8:14 am
I would advise strongly against creating a single procedure to retrieve all this data in a single step. As you stated, you will end up being forced to use dynamic sql and the performance is going to be horrendous. In these types of situations it is much better to divide and conquer. Have a stored procedure to retrieve the data for each type by itself. If you really want a single point of entry create a stored procedure as your driver and have it call the others. Then when you need to extend your process with a new type you create the procedure to retrieve that data and add it to your primary procedure. That is my 2¢.
_______________________________________________________________
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply