Francisco Tapia
fhtapia at gmail.com
Wed May 19 21:00:55 CDT 2010
Excellent description John, when I get a bit of time I'll write up some sudo code on how to do what you do but a bit more standardized. As it is having all your sprocs (stored procedured) in a central master database certainly helps with maintenance. I saw Shamil's reply and think that partitioned views could be a useful tool but really you are already segragating each dataset in each database. In your situation as I mentioned in the past might be a good passable reason why dynamic sql works. Its not always the case as most systems have user interfaces and any one of those guys could wreck your data. Some user defined functions to ensure parameters are scrubbed might prove useful to you to help strip harmful dynamic syntax. You mentioned that you have standardized fields and tables and you quite possibly have all of this worked out already. As I said I'll comment with more details when I get more time. On 5/19/10, jwcolby <jwcolby at colbyconsulting.com> wrote: > Guys, > > I use dynamic SQL a lot. I have received feedback from others on these > groups that "there is never > a valid reason for dynamic SQL", there is always a safer "better" way to do > this. > > I would like to lay out what I do and get some feedback on exactly how I > would do what I am doing > without using dynamic SQL. > > A couple of examples. > > I build an entire database for an order for my client. Each database has > some fairly specific > stuff, as well as data for that order, which I may need to use again for > another order. IOW, I > select X records, send a small subset of those records off to a client, then > months down the road > may need to send another subset of those records. > > Each order is based on selection criteria, and that criteria is different > for each order. So I > build a table on-the-fly. I have a common set of fields, then I have the > selection fields. I then > pull all of the data from a view into that custom table. > > So I have a view. I use code to get the names of the fields in that view > and I build a table which > has exactly the same fields as the view contains, using dynamic SQL. I use > the view to then append > the data into the table. Since the table has the same fields as the view, > it basically just appends > right in. Using dynamic SQL I am able to build any select query I need and > build a table which > exactly matches that view. > > Another example. I have many different databases which contain millions of > name / address / > demographic fields. For example I have a table of 20 million people who > have children, with fields > for whether they children in specific age brackets. I have another database > with 12 million records > of people who have cats and / or dogs. Another where... > > Each of these databases has a name / address component (fields) which are > standardized (by me) so > that the name / address field names are identical. I then have stored > procedures which build name / > address tables, append the name / address components out of the central > table into a temp table in > sorted order, grab just 2 million record chunks, and BCP the records out to > files on disk for > address validation. There is a similar set of stored procedures where I > pull the resulting > validated address fields back in to SQL Server into the same database and > perform other processing > inside of the database. > > Because I have standardized the field names, I can use dynamic SQL in stored > procedures to pass in > the name of the database and a table or view name for the export piece and > the import piece as well. > There are probably 5-10 stored procedures which perform the export process > as well as a like > number which performs the import. > > In fact I used to have "hard coded" stored procedures in each database which > did this stuff, but > maintenance was a nightmare. If I needed to change anything I had to go > into each database and > modify the stored procedures on a case by case basis. I now have a "master" > database where I store > my stored procedures and I can simply pass in the parameters to the stored > procedure to tell it what > database / view to use. One place to edit, one place to go to make > modifications. > > So that gives an overview of some of the ways that I use dynamic SQL. I am > looking for the "Why I > shouldn't" and what my alternatives are, or comments on when and why dynamic > SQL is useful (as I > find it useful) and how to go about making the resulting dynamic SQL safe(r) > to use. > > Thanks, > -- > John W. Colby > www.ColbyConsulting.com > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- Sent from my mobile device -Francisco http://sqlthis.blogspot.com | Tsql and More...