[dba-SQLServer] dynamic sql

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