May 4, 2011 at 12:22 pm
Hi All,
I wanted to know if anyone has managed to do the above?
Generally I try to avoid dynamic SQL due to it's relative complexity and difficulty to troubleshoot.
However, I have a feeling a client may well request that they have 1 stored proc which dynamically builds the query for the relevant table and executes the generated code. This would be in an attempt to reduce maintenance in the future.
My feelings are is that this would unnecessarily complicate the process and potentially create additional maintenance to maintain a metadata data table to add with the building of the SQL statement.
Agree/Disagree? I would like to hear your thoughts.
May 4, 2011 at 12:33 pm
I can't see that you'd have to build a separate metadata store, since sys.columns, sys.tables, et al, should allow for dynamic Merge statements to be built. However, it's going to be a "one size fits none" solution. Every dev tries it at least once in their life, just like "one true lookup tables". It always sounds "so promising", but the reality is that they all end up failing in the long run, and some even in the very short run.
If they really want to avoid having to build a stored procedure for each database operation, they should go with Linq or nHibernate or something like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 4, 2011 at 1:05 pm
I think you would still need a lookup table to idenify the business key? This cannot be physically defined on the table as a primary key as the table is holding type 2 changes.
Also in this particular sceanrio - something I didnt mention originally - is that the source and dest column names are different!
I totally agree with your other points.
May 4, 2011 at 1:23 pm
In that case, some sort of mapping would need to be done, either by a person at runtime, or by a mapping datastore of some sort. Yeah, that makes it an even uglier solution, because that's going to be harder to maintain and easier to forget about than specialized code will be.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply