Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
petebob796
Starting Member
35 Posts |
 Posted - 2008-02-11 : 11:07:46
|
| I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names. Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-11 : 11:17:25
|
| I didnt understand the scenario. What do you want to do with dynamic sql? |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-02-11 : 11:18:15
|
| If you have a design that bad, dynamic sql is pretty much all you can do.There are, however, two other options can explore:1) create a "normalized view" of the table. For example, like this:select '05' as Division, 2006 as Year, [0506] as Valuefrom Yourtableunion allselect '06' as Division, 2006 Year, [0606] as Valuefrom Yourtableunion allselect '05' as Division, 2007 Year, [0507] as Valuefrom Yourtable..etc ...That will let you query the View as if the table were well-designed. You would need to maintain this view as columns are added to your table. this could be very cumbersome to write, slow, and tough to maintain depending on how many divisions and years you have and how often they changed. But, this approach often works well. I have written many views over the years that "hide" bad table designs!2) Another option is to write a process that does use dynamic sql, but only initially to query the table and loop through it to output the data into a permanent, properly designed, well indexed normalized table. Then, you can query that new table efficiently and you would only need dynamical SQL to keep your tables in sync. You'd probably need to keep them in sync via a trigger or something along those lines, again depending on how often it changes.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
|
|
Column Name as parameter dynamic sql alternative