Stored procedure design and code generation
-
I work in a shop that is very strict about designing first, coding later. We use Visio for Enterprise Architects (heaven help me) for the actual entity design from which DDL script is generated during our coding phase. We also use Visio for designing our VB.NET class modules, from which skeleton source code is generated during our coding phase (Visio's UML code templates work nicely here).
However, I am unable to find a suitable (free or extremely inexpensive) tool that can be used to design stored procedure interfaces from which skeleton code can later be generated. The tools I have researched all want to "automagically" generate EVERYTHING - they examine a table and auto-generate all four CRUD procs in addition to VB code that executes the proc. But they don't appear to allow the designer to specify the actual parameter names or the template that is used for code generation.
So, here are my questions:
1. Can anyone point me in the direction of a flexible stored procedure design tool that will also generate skeleton code (without having to examine a table structure that doesn't yet exist)?
2. How do others who follow a strict "design before you code" process approach stored procedure design?
Cheers,
Andre
-
1.
I do not know of any personally, but like you would be interested to know of any.
2.
All my sp's are generated from txt files. I may use QA to test the sql syntax. I use isql to store the procedure in the database(s). Sometimes I use QA to load the txt file, edit, store and test and then save back to the txt file (but only in dev though).
I use a template file and edit it to put in proc name etc and then save as the new filename. Then add sql code.
I also have a .bat file that uses edlin to copy and edit the template.
In one specific project, since all the procedures are created from a standard set of 'building blocks', I wrote a vb app automatically creates a procedure with the right name, in the right place and with the sql code built from the options selected.
If I am feeling especially lazy I will copy a proc that does similar things and edit the name and code but this can lead to problems and errors, especially if you forget to change the proc name
Far away is close at hand in the images of elsewhere.
Anon. -
I don't understand what you would want generated if the table doesn't exist. How do greate an INSERT statement if there are no columns to insert? Most of the tools can generate, the regenerate code when you alter the table. I have used LLBLGEN (http://www.llblgen.com/pages/overview.aspx) which is an excellent tool I think. The templates it uses have changed significantly since I used the product so you may have to look and see if it suits you.
ApexSQL Code http://www.apexsql.com/ (which I haven't used) claims to have user modifiable templates which can be easily changed to produce code according to your standards.
Francis
-
Thanks for your replies. I have used both code templates (in SQL Query Analyzer) and code generators that create the entire DML code by examining a table. These are both very useful, but they're not what I'm currently looking for.
I'll try to explain by comparing to business logic modeling. When I design classes in UML, I have the ability to generate .NET skeleton code for those classes. The generated code contains no implementation logic, just declarations. I am looking for something similar for stored procedures where I can define the interface in a model and have the skeleton code generated with no DML statements.
In other words, I'm looking for a stored proc interface modeling tool that I can use during my design phase, as opposed to a full blown code generation or O/R mapping tool. I suspect that no such beast exists, but I thought I would ask.
Thanks again for your time and suggestions.
Cheers,
Andre
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply