[AccessD] dynamic sql

John,

<<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.>>

  The main thing is that with dynamic SQL statements, the execution plan
cannot be cached, so your loosing some performance there. 

  The only alternative you have would be to store and modify a script to
maintain the stored procedures themselves. 

  Others may disagree, but from the description of what your doing and the
way this is being used, there seems to be a lot more upside with using
dynamic SQL than a downside.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, May 19, 2010 4:19 PM
To: Access Developers discussion and problem solving; Sqlserver-Dba
Subject: [AccessD] 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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com