[AccessD] dynamic sql

Hi John --

I suppose all you do with dynamic SQL can be done using UDFs and partitioned
views. Not 100% sure about the latter as I have never used them. But I'd bet
there exist more effective and more manageable than using dynamic SQL
solution for your set of tasks - all within T-SQL for MS SQL 2008.

Sorry, I cannot comment more/participate actively in this thread as I'm
rather busy here with my customers' work but I will follow this thread with
interest..

Thank you.

--
Shamil

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, May 20, 2010 12:19 AM
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