EZStoredProc User's Guide

EZStoredProc User's Guide

Introduction

Aren't you tired of writing stored procedures for SQL Server tables that your application needs to have access to? Don't you wish that there is a tool out there that will magically generate the Insert, Select, Update, and Delete stored procedure scripts for you by simply right-clicking on a database or table?

Look no further! EZStoredProc (pronounced as "Easy Stored Proc") is that magical tool. Unlike the clumsy wizard provided by MS SQL Server Enterprise Manager, EZStoredProc does not add a number to the end of every parameter of the generated stored procedure. It is a lot smarter in handling primary key, composite keys, and identity column. It does not generate a stored procedure that passes in duplicate parameters when you have both the primary key and identity field as the same column. It is a lot more powerful and flexible because it provides many options for you to format and tweak your stored procedures.

Below are some of the highlights of EZStoredProc:

• Simply right-click to generate stored procedures for databases and tables.
• Execute stored procedure script against your database directly from EZStoredProc.
• Option to use identity field and/or primary key fields in Where clause.
• Generate Insert, Select, Update, and Delete stored procedures.
• Generate "Grant Execute" statement for each stored procedure.
• Return identity value (if identity field exists) from table.
• Allow flexible formatting and naming of stored procedures.
• Support either Windows Login or SQL Server Login.
• Support composite primary keys.

Below is a complete documentation on how to make EZStoredProc perform its magic. You should not have to refer to this User's Guide too often since EZStoredProc's user interface is simple, intuitive, friendly, and easy to understand.

Installation

The installation file EZStoredProcSetup.exe can be downloaded from our Download page. After you have downloaded the EZStoredProcSetup.exe file to your computer, double-click on it to start the installation process.

EZStoredProc requires Microsoft .Net Framework 2.0 to run on your computer. If you have not installed Microsoft .Net Framework 2.0, the installation program will automatically download it for you. Since the installation file for Microsoft .Net Framework 2.0 is quite large (23 MB), it might take up to one hour to download if you have a dial-up Internet connection. It takes about 8 minutes to download for a DSL connection.

If you already have Microsoft .Net Framework 2.0 installed on your computer, the installation program will not download it again. It will install EZStoredProc only. Since .Net Framework 2.0 is pre-installed with Windows Vista, you will not have to download .Net Framework 2.0 if you have Windows Vista.

You can check whether .Net Framework 2.0 is installed on your computer. If you prefer to download and install Microsoft .Net Framework 2.0 manually, please click here to download and install the Microsoft .Net Framework 2.0 installation file (dotnetfx.exe).

After you have downloaded EZStoredProc's installation file, double-click on it to run it. The installation program will first download and install Microsoft .Net Framework 2.0 (if needed). Then it will install EZStoredProc.

Once the installation process is completed, EZStoredProc will automatically be launched.  If EZStoredProc is not launched automatically, you can launch the program by clicking Start, Programs, EZStoredProc, EZStoredProc.  Or you can launch the program by double-clicking on the EZStoredProc icon on your desktop.

Another requirement for EZStoredProc to run on your computer is the existance of MS SQL Client tools (i.e. Enterprise Manager or SQL Server Management Studio). If you do not have these SQL Client tools installed, you will get an error message like below when you run EZStoredProc:



Don't worry, this error will go away after you install the SQL Client tools.

Registration

When EZStoredProc first starts up, a Product License form will be displayed.  If you do not have a User Name and a User Key, you can purchase them by clicking on the EZStoredProc Buy Now link. After you purchase your User Name and User Key, you can enter them in the text boxes and click on the Register button. If you do not have the User Name and User Key but would like to try the program, click on the Close button. You will be able to generate and view the stored procedure script but you will not be able to copy or execute it.

Make sure that you are connected to the Internet when you register. The registration process cannot be initiated if you are not connected to the Internet. If you get any errors during registration, you might want to try turning off any firewalls if they are on. You can turn them back on after you register. If you or your ISP (Internet Service Provider) is using proxy server to connect to the Internet, you might get an error during the registration process. The registration screen will then prompt you for the proxy server name and port number. If your proxy server requires a user name and password, you can enter them there too.

You can use the User Name and User Key only once. The User Name and User Key cannot be re-used after you have registered with us. Please do not share them with others. If you need to move your license from one computer to another, you can perform the "un-registration" process. To un-register your license, you will need to go to Help, License Information menu item on the main screen. Click on the "Unregister" button and your license will be removed from your current computer. Make sure that you are connected to the Internet when you un-register. After the un-registration process is completed successfully, you can then register the product on a different computer.

If your computer crashes and you need to re-register the program on your computer after you reformat your hard drive, you can write us an email with your User Name and we will reactivate your license for you.

Main Screen

The next screen is the main screen for EZStoredProc.  You can connect to your SQL Server, right-click on the database objects to generate stored procedure scripts, and execute your scripts here. You can also change your settings from the Tools, Options menu.

Connect to SQL Server

If you have previously registered to a SQL Server from your machine, all the server names should be displayed in the SQL Server Name combo box. Select the server you need to connect to and click on the "Connect to Server" button. If you have SQL Server Management Studio (SQL 2005), you will need to type in the server name and instance name into the SQL Server Name combo box. Default login method is set to Windows Login. If you have a SQL Server user login that you would like to use, you can click on the "SQL Server Login" radio button and type in the User Name and Password. Click on the "Connect to Server" button again to log in using the SQL Server user login.

Generate Stored Procedure Script

On the left panel, you will see a list of databases on the SQL Server that you have selected. To generate stored procedure script for all the tables in a database, simply right-click on the database and select "Generate All Stored Procedures for Database". The script (with Insert, Select, Update, and Delete stored procedures) will be generated and displayed on the right panel.

You can click on the "+" sign on the left of each database to expand the node to show all the tables below it. You can right-click on any table and choose "Generate All Stored Procedures for Table" to generate stored procedure script for the selected table.



If you would like to generate a particular stored procedure under the table, just click on the "+" sign on the left of the table to expand the node. You will find Insert, Select, Update, and Delete stored procedures. Right-click the one of the stored procedures and choose "Generate Stored Procedure".

Once the script is generated on the right panel, you can execute it directly from within EZStoredProc by clicking on the "Execute Script" button. If you would like to execute a portion of the script, you can highlight the text that you need to execute and click on the "Execute Script" button (much like the behavior of Query Analyzer or SQL Server Management Studio). To verify that the stored procedures are created, open up Enterprise Manager or SQL Server Management Studio to find the stored procedures in the appropriate database. If you have Enterprise Manager or SQL Server Management Studio open while running EZStoredProc, you will need to refresh the stored procedures in Enterprise Manager or SQL Server Management Studio to view them.

If you prefer to copy the script and execute it on Query Analyze, you can right-click on the right panel and select "Copy" from the context menu. You can then paste the script onto Query Analyze to execute it. Please note that if you have a trial version of EZStoredProc, you will not be able to copy or execute the generated script.

Tools, Options

There are many options available for you to change the way the stored procedure script is generated. Most users will be happy with the default settings and there is no need for them to tweak the script. However, if you feel like changing some of the settings, you can go to the "Tools" menu and click on the "Options..." menu item. The Options screen has 5 different tabs: "Formatting", "Output Stored Proc", "Grant Execute", "Return Identity Value", and "WHERE Clause".

Formatting

Under the "Formatting" tab, you can choose to put square brackets "[ ]" around table name, role name, stored procedure name, or field name. For example, if you check the check box for "Put [ ] around stored procedure name", your generated stored procedure will look like this:

CREATE PROCEDURE [sproc_TableCKOnlySelect]

If you uncheck the check box for "Put [ ] around stored procedure name", your generated stored procedure will look like this:

CREATE PROCEDURE sproc_CustomerInsert

If you check the check box "Use 'dbo' for stored procedures", you stored procedure will look like this:

CREATE PROCEDURE [dbo].[sproc_TableCKOnlySelect]

If you do not check the check box, your stored procedure will look like this:

CREATE PROCEDURE [sproc_TableCKOnlySelect]

You can also default all input parameters for Inset or Update stored procedures to Null by checking the check box. We do not recommend checking this check box if you plan on passing in every field during Insert or Update.

Output Stored Proc

Under the "Output Stored Proc" tab, you can specify your own prefix and suffix for the stored procedures. For example, you have a table called Customer and you would like to make your Select stored procedure look like this: "myStoredProc_CustomerRetrieve". "myStoredProc_" is your prefix and "Retrieve" is your suffix to your Select stored procedure.

You can also surpress the generation of certain stored procedures by unchecking the check boxes next to them. For example, if you do not want to generate any Delete stored procedures, simple uncheck the check box next to the option "Generate DELETE stored procedures". You will not see the Delete stored procedure when you expand the table node on the left panel on the main screen after you have checked the check box.

Grant Exec

You can optionally suppress the generation of "Grant Execute" statement for each stored procedure. Enter the appropriate user/role in the "Grant Execute access to user/role" text box if you choose to generate the "Grant Execute" statement. If you check the check box and you leave the user/role as "public", here is how the statement is going to look like:

GRANT EXECUTE ON [dbo].[sproc_CustomerSelect] TO [public]

Return Identity Value

If your table has an identity field and you would like to return the identity value in the Insert stored procedure, this check box should be checked. The generated statement will look like this (with CustomerID as the identity field):

SELECT Scope_Identity() As CustomerID

Scope_Identity() is the new and prefered way of returning identity value over @@Identity. It works just the same as @@Identity but better because it is not affected by any activity that occurs due to trigger. @@Identity might return an identity value from a different table due to triggers that are fired when the stored procedure is executed.

WHERE Clause

This is a fantastic feature that many stored procedure generators do not have. A lot of smart is built into EZStoredProc to intelligently handle tables with primary key, composite keys, and identity field. For example, if you check the check box for "Use Identity field (if exists) in WHERE clause for SELECT, UPDATE, and DELETE stored procedures", and uncheck the check box for "Use primary key or composite keys (if exist) in WHERE clause for SELECT, UPDATE, and DELETE stored procedures", EZStoredProc will generate stored procedures that take the identity field as one of the input parameters and use it in the WHERE clause of the Select, Update, and Delete statements. If you have a table that has primary key field but does not have identity column, EZStoredProc is smart enough to use the primary key in the WHERE clause of the Select, Update, and Delete statement.

If you check both check boxes, EZStoredProc will use both identity and primary key fields as the input parameters and use them in the WHERE clause. If some of your tables have either the identity or primary key fields but not both, EZStoredProc will be intelligent enough to use the appropriate fields as input parameters in the WHERE clause. To find out how robust EZStoredProc is, you can test drive it by creating tables with different combinations of identity field, primary key, composite keys, etc. You can even overlap your composite keys with your identity field and you will see that EZStoredProc handles all of them gracefully, no matter what you throw at it.

Please note that if you have a table without any identity field, primary key, or composite keys, the WHERE clause will not be generated, which means the Select, Update, and Delete statement will affect all the records in the table! You should always avoid creating tables with no key fields, unless you absolutely have to.

Help

If you need help using EZStoredProc, click on the Help menu on the main screen and choose User's Guide menu item. You need to have Internet connection to view the User's Guide online.

Uninstallation

If for any reason you need to uninstall EZStoredProc, you can go to Start, Programs, EZStoredProc, and choose Uninstall EZStoredProc. Another way of uninstalling is by going to Start, Settings, Control Panel, and double-click on the "Add or Remove Programs" icon. Locate and click on the EZStoredProc item and click on the "Change/Remove" button on the right. Follow the instructions to uninstall EZStoredProc.

Supported Hardware

Intel/AMD processor.

Supported Operating Systems

Microsoft Windows 11 Microsoft Windows 10 Microsoft Windows 8 Microsoft Windows 7 Microsoft Windows Vista Microsoft Windows XP