Hans-Christian Andersen
ha at phulse.com
Wed May 19 16:31:35 CDT 2010
Hi John, I'm speaking from beyond the world of Microsoft - the world of PostgreSQL, MySQL and so forth. Two main reasons that spring to mind why dynamic SQL can be a bad idea is: 1. Query caching - This may or may not be the case in your instance. Like I said, I can't really speak for MSSQL, but dynamic sql could very well cause performance problems, if the query cache is unable to cache things properly. 2. Security - Allowing your application to inject SQL into your queries can leave your application dangerously exposed. If this isn't interfacing with a public application, then you don't really have much to worry about. But if it is, little Bobby Tables might very well destroy your database. I prefer to go with properly prepared statements, validation and parameterization, over re-usuable hacks/functions/procedures... Finally, and this is more of an aesthetic point of view, when your code gets to a point where there is too much dynamic generation of "stuff", it can get quite complex to maintain... especially for anyone else who has to look at your code/stored procedures and figure out how it all hangs together. It's just like recursion... it's quite cool when it works, but it's a nightmare to debug when it breaks! [image: exploits_of_a_mom.png] Hans-Christian Software Developer, UK ----------------------------------------------------------------- tel: +44 (0)782 894 5456 e-mail: hans.andersen at phulse.com www: nokenode.com ----------------------------------------------------------------- Unique Gifts, Collectables, Artwork ----------------------------------------------------------------- Come one Come all to www.corinnajasmine.com ----------------------------------------------------------------- On 19 May 2010 21:18, 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 > >