[dba-SQLServer] Stringing USPs together

Most of the queries I need to run are one liners.  Make table.  Make table.
Append table.  Update field.  Update field.  SELECT data.  

There are a couple of areas that need input though.  For example I need to
import data from a csv file.  If the data comes in then continue else.  I
could hard code the path but I would rather not.  The full procedure is:

.	Create an order directory in the path PSM\Orders\ using the order
number as the directory name.
.	Copy the order spreadsheet to the directory 
.	Copy the count workbook to the order directory
.	Copy the order page into the count workbook
.	Copy the Count database with all of its views for counting and
building the order, named to the PSM order number.
.	Check for and build indexes on any fields in tblHSID used in WHERE
clauses that do not have one already.
.	Create views which reference the tables used for the order, for
example HSID and ZipCodeWorld (automatically done now with the count
database)
o	vHSID - HSID selection data fields
o	vAZHSID - HSID Accuzipped
o	vZCWZipsUsed - Zip Code World zips used (no prisons, military etc)
.	Create the individual where clause views and test.  Often the
queries use WHERE clauses with many different fields including age bands,
income bands and then other individual fields such as skiing or dogs or
boats etc.
.	Join all of the individual selection queries by PK to create the big
selection criteria query, pulling PK for use later.
.	Create a ZipCodeWorld query if we will be narrowing by zips, CBSA
etc. pulling just the zip to join to the select query.
.	Output to Accuzip
.	Input from Accuzip.  Name table "tblOrderRaw"
.	Create tblOrder with the same structure as tblOrderRaw
.	Add HashAddr field, varbinary(200)
.	Add DateUsed field, DateTime 
.	Add RandomNum field, Integer (random) 
.	Copy all valid records to tblOrder.  DPV='YN' and DPVNotes = 'AABB'.
Delete PO boxes and blank addresses.  Cleans up after Accuzip process.
.	Update HashAddr field in tblOrder with update query
.	Run dedup query on tblOrder.  This dedupes the data.
.	Create vOrderSortByRandomNum.  This provides the random selection.
.	Create vOrderNewTopN in the SQL database based on the
vOrderSortByRandomNum.  This selects the top N records not already marked as
used.
.	Copy qUpdOrderNewTopN - Selects a Top(N) number of records and marks
them as used
.	Copy Access database to order directory
.	Modify to point to the current database in sql server.  This Access
database is used only for creating the flat file export and will go away
when the export out of SQL Server is up and running.
.	Export using the export spec in Access

Even in this document, some steps are still several steps in one.  And of
course some of this stuff just isn't a good candidate for SQL Server to do
(make directories, copy files to subdirectories, insert pages in
spreadsheets etc.

And of course this isn't one big automateable process, sometimes I have to
go modify views in the middle.  Stuff like that.  But if I can get the last
1/2 automated I will be miles ahead of where I am now.  The processes after
"Input from Accuzip" is pretty much all automateable in one big script.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Fred Hooper
Sent: Monday, March 24, 2008 6:49 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Stringing USPs together

You create the stored procedure with an input parameter. Then, when you call
the sp you provide the parameter. E.g.

Exec sp_process_a_database DatabaseName

The "DatabaseName" is assigned to the parameter and then processed.

Unfortunately, that parameter can't be used for a field, table or database,
as this information is needed to optimize the execution plan. So if you want
to use it for one of these you'll need to use dynamic sql, which is just a
different container than a DoCmd.RunSql, because it can't do anything with
an execution plan.

If you're just using the sp for different databases, don't fully specify
tables and you can create the same sp in different databases.

Fred