[dba-SQLServer] Dynamic SQL

Hi John:

When I want to find if a table exists in my database you can run the
following code:

CREATE  PROCEDURE Create MyDatabasetTable  AS

/* if exist do the following */
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MyDatabasetTable]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)

	drop table [dbo].[MyDatabasetTable]

else /* create the table */

CREATE TABLE [dbo].[ MyDatabasetTable] (
	[ReferenceNumber] [varchar] (10),
	[Status] [varchar] (2),
	[Account] [int] NULL,
	[ClientCode] [char] (10),
	[InvoiceDate] [varchar] (12),
	[Amount] [money] NULL,
	[IDNumber] [varchar] (16),
	[ExpiryDate] [varchar] (8),
	[Source] [int] NULL,
	[Type] [int] NULL,
	[TransactionID] [varchar] (20),
	[ProcessCode] [char] (1),
	[OperatorCode] [char] (8) 
) ON [PRIMARY]

GO

The above example does not make much sense as in why would someone delete a
table if found and create if not but this just a code sample.  It shows the
best way to create and delete tables within an application that is running a
MS SQL server. Just call the appropriate stored procedure.

HTH
Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, October 01, 2007 5:03 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Dynamic SQL

Folks,

There has been much discussion re using dynamic SQL to perform operations on
SQL Server, vs. using stored procedures.  Let me give you a rundown on my
situation and I would then like opinions on strategy.

This is the "big database" operations I am discussing.  I get DVDs full of
raw data, typically many different text (flat or CSV) files, which I
assemble into a directory.  I am using VB.Net to iterate through the
directory and import all of the files into SQL Server.  My code:

1) Creates a new database if none exists for this import.
2) Creates a new "master" raw data table if none exists
3) Creates a temp table for each data file
4) Imports the data into the temp table.
5) If the complete data file imports without errors, I append the temp to
the master table
6) If the append succeeds I delete the temp table
7) If all of the above succeeds I move the data file to an archive directory
8) I log each step of the above, both errors if any and a general memo of
steps, how long they took etc and when done I write that log file to disk.
Someday I will write that log data to disk.

Steps 1 through 6 is all "dynamic sql" that I build up based on the SQL
Server name, the database name, the table names, and even the field names.
Doing things this way allows me to have a class that knows how to build
databases, tables and indexes, and also perform action queries to perform
all of these actions.

My question is simply whether this really needs to be moved to the SP
paradigm?  My database runs on a server here at my office behind a hardware
and a software firewall, although of course I am not a notwork guy so in the
end how secure it all is is questionable.  To this point there is no one
accessing the database from outside of my office and in fact to this point I
am the only one accessing the database at all.

If I were to move to the SP paradigm I guess I would need to design and
store all of these SPs into a master database and run them from there,
passing in the various part names?  I really do not want to go there "today"
as I have work to do and this would be another learning curve and a lot of
development effort.

So, opinions on the necessity in my case?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com