At my current job we depend a lot on stored procedures for our web applications and sometimes it gets really boring when we have to write and re-write stored procedures for different databases and tables as demands increase. This pain has me looking for an easier and faster way to generate standard stored procedures that would meet our database design specifications. A few years back I stumbled on some source code developed by e Tek Global that would create stored procedures, but there were some short comings with the brilliant tool because I had to know and pass my parameters to make the procedures usable since it did not have a front end GUI. With my hunger then to still make my job easier I went Googling and bought a tool with a GUI but it still it had its own issues.

A few days ago I received an email from e Tek Global regarding a new SQL 2005 add-in tool to do a similar job as the one they had developed previously. With the descriptions it looked attractive but I was not sure if it would be good enough to replace what I had. I decided to give it a try with the evaluation copy. I was amazed by what the tool could do despite some features not being function on the evaluation copy. I was still able to generate powerful, well designed stored procedures. So here I am giving you a sneak peek into SQL SP Generator.

 

SQL SP Generator is a SQL 2005 Management Studio Add-in that allows SQL developers to generate any kind of stored procedure from a single or multiple tables in a database. In addition, it also allows SQL developers to define their standards like procedure prefix and suffixes and permission assignment to different users/roles.

This tool is downloadable for a 15-day/45 instances free trial at:

E Tek Global Inc website

C|NET Download

TopShareware.com

In this part we will look into the install and how the application ties up to SSMS.

But before we look into it, let me be clear on one thing, this is not a Windows desktop application. It is strictly an add-in on SSMS for SQL 2005. The interface for SQL SP Generator is very professional, following the same look and feel of Microsoft’s SQL 2005 SSMS Wizards. It has a welcome splash screen which can be hidden by checking the hide checkbox.

The wizard interface has the following steps:-

Welcome screen – Gives you some information on what the wizard will do.

Choose Options – This screen allows you to define your standards for generating the stored procedures.

Output option – Allows you to select your preferred output for the generated code. The options are Script to File, Script to Clipboard and Script to New Query Window.

Select Table – This interface has a cool look and feel that allows you to select the table(s) for which you would want to generate stored procedures for. It even displays the parent table for the selected table (I will explain this feature in details later).

User Permissions – The developer can even script the user permissions for the stored procedures that will be generated by the Stored Procedure Generator.

Summary – This screen finally displays all the selected options.

In general, the SQL SP Generator developers have used the same format and layout that is used in Microsoft SSMS. If you use the SSMS generate scripts wizard, you will get some idea about it.

 

After the simple install, you can launch the tool from your SQL Server Management Studio 2005 by right clicking the database or a specific table and select Generate Stored Procedure using SQL SP Generator as shown in Figure 1 below.

Figure 1

For a single table you can right click on specific table as shown below.

Figure 2

 

 

 

After the code is generated, it will be pasted to your area of speciation with my ideal location being SSMS Query Editor which shows a complete integration with SSMS 2005.

 

This tool allows you to define and standardize your procedure names and goes a step further to allow you to specify any keywords that need to be removed from the Stored Procedure name. For example, you might not want the prefix like "tbl" or "tlkp" in the procedure names, you can specify those keywords in the Choose Options Window as shown below.

Figure 3

 

If you look at Figure 3, you can see that the tool allows you to define the overall procedure prefix and suffix. For example, you can define pr_ as your prefix, which will attach pr_ to every procedure that will be generated. The Remove Keywords from Table allows you to define the words that should be excluded from the procedure name.

The tool makes it even more procedure specific where you can define the prefix and suffix for your Insert, Update, Delete, Get All and Get Single procedures.

Finally, the Choose Options step allows you to select whether you would like to have error handling on your script, whether you want to create Drop Script and also specify user permissions. The error handling will include the Try Catch block that has been introduced in SQL Server 2005.

These steps make my coding life easier because all my specified options are locally stored on my machine where it remembers my settings next time I am using the tool.

This gives the tool a big plus over others in the market.

Most of the tools in the market will allow you to create a file from the options you have selected. SQL SP Generator goes one step further to allow users to select the destination of the script as shown by the following figure.

Figure 4

The options are Script To File. In this option you need to specify the sql file name and path. You can also define the file format for the SQL File. The options could be Unicode Text or ANSI text.

You are also allowed to copy the stored procedures that are generated to the clipboard.

Finally, you can just select the default to display the SQL Script in SQL Server Management Studio itself.

Again, this is following the Microsoft Wizards in SQL Server Management Studio. Check the Generate Script wizard and you will see the similarities.

 

This option was another brilliant development; the tool seems to be using Infragistics Controls on this screen. It gives you more options that are not available in other tools that I have used previously.

Figure 5

Some of the features that are displayed in Fig 5 above include:

You can define which stored procedures you would like to generate. The options that are displayed are based on the selection on the Choose Options screen. If you selected only to generate stored procedure for Insert and Update then you will not see the columns for Select, Select All and Delete.

You can filter the list by specifying the table name. As you see in Figure 5 I have a lot of tables displayed. If I start typing name of the table wanted just below the Table Column, SQL SP Generator will start filtering based on the word typed as show on Figure 6.

Figure 6

 

 

You can specify where you want to search the entry by clicking the “A” icon next to the blog.

Figure 7

 

You may have noticed that I even moved the columns around in the screen above. You can define custom filtering by clicking the dropdown and then a pop-up will be displayed.

Figure 8

 

Specifying columns and column Alias: You are even allowed to select the column names that need to be included in the stored procedure. If you would like any of the columns to be aliased, you can do that too. Also, if any of the fields are a foreign key, you can include the parent table in this select all and select single stored procedures as shown below in Figure 9.

Figure 9

Defining the default values: You can specify the default values for specific columns for the Insert, Update and delete procedures.

This step is the most flexible table selection I have seen so far. There are so many features on this screen that in order to explain all those features will use up one complete article.

 

On this screen you can define who has security permissions on the stored procedure that is generated. Some of the other tools do this, but it allows you to give execute permissions to a specific user. This Stored Procedure Generator goes beyond that and it allows the developer to specify all types of permissions for the Stored Procedures.

Figure 10

Also, you can give security access to various users not only one specific user.

The summary screen sums up all the selections you have made.

Figure 11

Hopefully no you get the idea on what exactly will be generated by this Stored Procedure Generator.

The support for this tool is really good and quick. Generally, the turnaround time is less than 1 business day. You just send them an email at sqlspgenerator@etekglobalinc.com and they are ready to assist you. They are open to suggestions and enhancements for the tool. e Tek Global Inc is into Microsoft Consulting and they are also Microsoft Certified Partners. They make sure that the customers are kept happy.

This is one area where this tool is totally undervalued. Most of the tools that are available in the market are listed for at least $40 per copy. SQL SP Generator is for only $19.95 per license. It is as good as giving away the product for free. For the features provided in this tool, it can get way more money than the listed price.

Though this tool has all the features that are required by SQL Developers; there are a few things that they could do more to enhance the features list. Again, this is just my perspective and it is not that important or a break point for this tool.

1. Allow Computed columns: Sometimes we need to have one column in select all or select single to have computed columns that are generated from other columns. But this feature can be used rarely.

2. Better Column headings on the Table Selection: A beginner could get confused with it but again, it is part of training and you will be used to it.

3. Specifying the return output: This could be useful if we are allowed to specify whether we would like some kind of error code or even identity column value to be returned back to the calling function.

The other point where other tools might score against this product is the database support. SQL SP Generator is specific only for SQL Server. If they open it up for other databases like Access, Oracle, or MySQL, they will really rock with the features provided.

Overall this product is a great product that each SQL Server Developer needs to have. This product goes beyond the expectations that are set by various code generation tools for SQL. This Stored Procedure Generator will really save us development time.

Company: e Tek Global Inc. Website: http://www.etekglobalinc.com

Free Trial downloads available at the following

e Tek Global Inc. website

C|NET Download

TopShareware.com

Purchase copy at:

http://www.etgstore.com/store/  Use PayPal. If you use SQLSPLAUNCH as a discount code then you get $6 off. Visit the company's website for more information.

http://www.snowcovered.com