Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jcraddock/datadrivenarchitecture.asp

Dynamic code such as this is something my company try to practise and preach. In my current data warehouse project we have built a metadata layer that lists the warehouse tables that we process data for. By employing a standard naming convention for the SPs that do the ETL we are able to generate a list of tables for which data needs to be processed and then dynamically call the requisite SPs. This has the added advantage that we don't have to call ETL SPs that we don't need to (perhaps because not all source data is supplied every day of the week - as is the case on the project in question).

This takes alot of work initially to set up the metadata and the code to take advantage of it but thereafter adding new bits of functionality is very easy. Not having to explicitly call each piece of new ETL code is fantastically powerful and gives us a quick turnaround of chalkboarded design to a full implementation.

Definately the way forward.

Thanks for the article...I enjoyed reading it.

I agree that this is pretty nifty; I use a similar getup for generating reports using SQLMail.  That said, I've found some problems using the idea:

1. No dependencies -- The SQL statements are not "in" SQL's knowledge, so determining if something breaks if you change the name of a table/column is not as easy as a sproc.
   This one has been a biggie.  We have a report that transfers data from one database to another with some calculations.  There is no way to do "select lhs, measure, scale, offset" when all the calculation code is in the query in the table.
2. Query isn't cached -- Dynamic queries are a bad thing (as was told to me... my db isn't big enough to care)

That said, I've mitigated some of the problems by adding Yet-Another-Layer:

1. There is a table that has queries, descriptions, and I/O parameters
2. The queries *mostly* point to stored procedures or views.  They sometimes have "formatting" peices (eg, casting everything to nvarchar & concatenating it so it can be handed off to SQLMail), but they don't reference anything but the sprocs.

The upside is that DB dependencies are tracked in SQL server for you, and you have a "List of queries to do things."

The downside is that you really should make a new stored procedure for the joining operations -- I don't know how effective joining a bunch of views together would be, so its not as "reusable/configurable" as the scheme in the article.

Although all of this is beginning to sound very similar to the master database tables...  I've been thinking that I could create a few simple "filter" tables (eg, Object X, Column Y is visible as Name) that I would join with the sysobjects/syscolumns/whatnot.  I guess this wouldn't be at all portable among databases

Here ya go:

DOWNLOAD

I've been researching and working the last couple of weeks on Extended Properties and metadata, good timing.    I'm looking forward to working with the samples, however while the new file provided ClassFile.vb it also gives a bad zip file offset error when unzipping and does not unzip DataDrivenArchitecture.ERI or DataDriveArchitecture.sql. The original file didn't have them included either.

Thanks

We have been using this idea internally for years in a winforms app.  Our base product is very soft and intelligent, allowing you to basically design the database and not have to worry about the UI.  Once the data structure is in place, you just add items to the menu that point to a view/table/sp and the interface is done.

The interface does things like automatically give a lookup window for FK columns, a date picker for datetime colums, a checkbox for bit, provide all neccsesary navagation buttons, etc.  You can then change column/form lables, hide columns, add hyperlinks, add buttons, etc.  Parent/child layouts can be specifically defined, or can be told to generate automatically based on partial PKs containing an FK to another tables complete PK. 

There is a lot more functionality we've built in over the years, allowing us to very quickly develop and deploy applications.  A side effect of this has become the ability to throw our tool at existing databases to provide extended functionality for existing applications instantly.

We are in the process of polishing it and are going to offer it for sale toward the middle of this year.  If anyone is interested you can get some minimal further info at http://www.datasofttools.com (excuse the unfinished site, as I said we are looking at mid year to release to public).