Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Auto-Stored Proc Generator Suggestions

Author  Topic 

moviewatcher
Starting Member

3 Posts

Posted - 2004-06-18 : 13:31:20
Hi.

I'd like to find a tool that will auto-generate stored procedures from a given database schema that already exists.

I'd like the typical INSERT and UPDATE procs created for each table (or maybe the result of a given query I provide) to save time as I create a new database design.

If anyone has recommended stored proc wrappers for .NET - I need suggestions for those too. :)

Thanks-
Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-18 : 13:33:43
Damian likes Codesmith:

http://weblogs.sqlteam.com/damianm/archive/2003/09/29/185.aspx

Tara
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-18 : 13:44:13
How can you auto generate SPs without the business rules that they have to support?
Hope you don't mean insert, update, delete SP for each table? Not much point in building a system like that but you could easily build a generator for them.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

moviewatcher
Starting Member

3 Posts

Posted - 2004-06-18 : 14:04:58
This is what I mean. If I can get something to generate the basic stored procs, I can then edit them as needed. I thought it might be more interesting to see what is out there to help this process than to do it the old-fashioned way (all by hand, manually).

Looks like CodeSmith might be a good starting point for me. Tutorial describes how to auto-gen procs.

Thanks!
Dave
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-18 : 14:14:18
For Codesmith, you can create templates and base your stored procedures on those templates. It does a lot more then just simple insert, updates, etc. It's actually a pretty kewl tool.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-18 : 15:00:12
I found this lying around...probably in response to a post...

Never used it though...like they are saying here, you'll need to do more...

Just talked to a buddy of mine who got a job where they store all of the business logic in the .net layer, not is sprocs...

To me, that's a bad idea.

Here's the rope



USE NorthWind

DECLARE @TBName sysname, @TBName2 sysname

SELECT @TBName = 'Orders', @TBName2 = 'Orders2'

SELECT SQL FROM (

SELECT 'INSERT INTO ' + @TBName2 + ' ( ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ') ' As SQL, TABLE_NAME, 2 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
UNION ALL
SELECT 'SELECT ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ' FROM ' + TABLE_NAME As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order







Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-18 : 15:04:43
I've got to find that MS article that says not to store the business logic in sprocs due to performance. The article said to pretty much only put the SELECT/INSERT/UPDATE/DELETE in sprocs and the IF/THEN logic in the application layer.

Tara
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-18 : 16:14:16
Yeah - I had an Andersens/Microsoft partnership try to expound on that to me some years ago - shortly after saying that all they needed was to know the business we were in to design the system. So they woud implement the same system for our 1000 transactions per year as those companies doing that per minute?
It was all the rage when n-tier became a fashionable term - unfortunately most people realise it doesn't work now but it was fun. Did help the hardware guys selling web farms to do all the application processing though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

moviewatcher
Starting Member

3 Posts

Posted - 2004-06-18 : 16:16:38
This is pretty much what I do as well - let the procs do the actual database interaction (UPDATE/INSERT) but keep the biz logic in a .NET layer (typically assemblies).

I'm hoping CodeSmith will simply do much of the work of building out the procs (and maybe now even some of the .NET code to call the proc). I'd rather tweak as needed, than write from scratch.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-18 : 16:27:10
lol.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-18 : 16:30:49
This was a recent article actually. It said that SQL was slow when it came to the IF/THEN logic and that SQL should do as little of this as possible. Both scenarios would have to be tested. I'm sure this is a rather heated debate though. Don't shoot the messenger, I'm just telling you what I've read.

Tara
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-06-19 : 02:40:15
quote:
Originally posted by nr

How can you auto generate SPs without the business rules that they have to support?
Hope you don't mean insert, update, delete SP for each table? Not much point in building a system like that but you could easily build a generator for them.

Have to say I disagree with you - and I did last night too, but something ate my post :(

We auto-generate SProcs here for GET, SAVE and DELETE and "templates" for a MATCH function and a FIND SProc.

GET is basically a SELECT * [it does list the columns though!] from a table give the PK columns.

We use this for record maintenance forms - which usually want all columns for a given row.

How long does it take to write an ASP page to maintain a poxy little table for editing a Country code and Description table? Well, actually it doesn't here - we just press a button and get the SProc, the Web pages and Uncle Tom Cobbly and All.

SAVE is a bit more interesting. It has parameters for every column; they are all declared as VARCHAR [except TEXT ones ...] with a DEFAULT of NULL.

If a value is passed it is used - first being converted to correct type if non-varchar.

If EMPTY STRING is passed then the column is set to NULL

If NULL is passed (this never happens in practice, so it only gets there by the DEFAULT on the SProc parameters) the current value is left alone.

The SAVE Sproc does what we term an UpSert - UPDATE if exists, otherwise INSERT. There is also a parameter to force MUST NOT EXIST - INSERT and MUST EXIST - UPDATE

The DELETE just deletes a row given the PK column(s) as parameters.

The generation of these "standard" SProcs uses House Style, Error handling, Logging, Debugging code etc. Saves a lot of time and creates very good consistency.

The SAVE SProc has a section for Business Rules, and of course some of them get well and truely mangled (needing to take additional parameters for associated records that are created on the fly, and so on).

However, on the 80:20 rule, loads of our SProcs have not needed to be changed from the automatically generated originals.

When we add a column to a table we rengerate the Sprocs and use a text comparison tool to merge the changes - leaving the Business Rules intact but ensuring that we add the additional column to just the right places in the parmeters, logging, insert and update statements.

Also, if we want to INSERT a row to some table, from within an SProc, we call the table's SAVE SProc. Ditto for UPDATE. This means that all such Insert/Update actions go through the appropriate sprocs and get logged / validated / RAISERROR'd et al., rather than just being dotted around the code as INSERT/UPDATE statements which would need much more finding & sorting out when things change.

Kristen
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-19 : 13:30:31
For things like maintaining lookup tables where you have to perform row by row operations it's fine but only for those tables where you just have a description of an item which is a business entity - anything more complicated could cause problems and it only works if you keep the lookup tables separate. Another way is to hold all the lookup entities in a single table and have a single app and sp to update it - have a drop down list for the entity. Means compound keys for the lookup value but can make the application and database structure a lot simpler. You can also leave them on separate tables (or some of them) and have the SP get and update the values from the tables. Still makes the app simpler and removes the database structure binding.

Consider for telecoms billing system where you have a rate for a call to a destination. To rate a cal with tye business logic in the middle tier would mean retrieve a call retrieve a rate update the call with the value. Repeat for all calls.
Put the business logic in the SP and you get update the calls table from the rate table joining on the destination for all calls that haven't been rated.
The second will be thousands of times faster and the first couldn't hope to cope with anything but a few calls.

(Note you wouldn't rate calls like that in real life because things are more complicated and that wowuld be slower than it needs to be too).

Other problem with the scenario you have is that the application is bound to the data structures. If you think you have business logic in the application layer then the database will need a table per business entity instead of tables to support business entities. Either that or you get transactions held from the application layer and associated lack of performance and probable deadlocks. Also means that if you neeed to change the database structure (e.g. for performance) you will have to change the application (it's bound to the database structure).

It's basically an application centric way of building a database rather than acknowledging that the process control and data storage have different requirements.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-19 : 13:48:56
quote:
Originally posted by tduggan

This was a recent article actually. It said that SQL was slow when it came to the IF/THEN logic and that SQL should do as little of this as possible. Both scenarios would have to be tested. I'm sure this is a rather heated debate though. Don't shoot the messenger, I'm just telling you what I've read.

Tara



Yep that's true but it has nothing to do with keeping all business loging outside the application layer. Anything that has to update data will be a lot slower in the application than an SP. Anything that increases the amount of data trasnported to the application layer will have a lot more affect than if/then logic.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -