Building a Stored Procedure Generator Creating basic data access stored procedures is time consuming and boring work. Relieve the tedium by writing code that writes these stored procedures for you. We all know that the most efficient way to access data from a database is to use stored procedures. For most applications, these stored procedures follow the same basic design whereby you list every field that you need to retrieve or save. For large tables or a large number of tables, writing these stored procedures can be very cumbersome and prone to typographical errors. | " |
A stored procedure generator is basically an application that, when run, creates a stored procedure script.
| " |
This article demonstrates how you can build your own stored procedure generator. You can then modify and enhance this stored procedure generator to tailor the results to your stored procedure style. Define Your Stored Procedure Structure Stored procedures that perform complex and unique operations are best done manually. But most stored procedures perform basic database operations such as retrieve, insert, update, and delete. Often, these operations are accomplished with stored procedures that look identical except for the actual table and field names. These types of stored procedures are prime candidates for a stored procedure generator. A stored procedure generator is basically an application that, when run, creates a stored procedure script. You can then save the script within a Database project and run it against your database to create the stored procedure. Details on using Database projects are provided later in this article. The first step in building a stored procedure generator is to define the basic structure for your most common types of stored procedures. Let's use the Customers table from the Northwind database as an example. A script that defines a stored procedure to retrieve all of the fields within a table given a unique ID might look like this: CREATE PROCEDURE dbo.CustomersRetrieveByID_sp
@CustomerID nchar(5)
AS
SELECT
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
FROM Customers
WHERE CustomerID = @CustomerID
This example uses a five-character string for the primary key, but best practices often dictates using a unique, meaningless, numeric key (such as that defined with an Identity column in SQL Server). If your tables use a numeric key, just change the data type of the stored procedure parameter. Another common retrieve stored procedure is one in which you collect all of the key values for all of the rows in the table. You can then bind the result of this stored procedure to a drop down list or other user-interface element for user selection. For example, to define a stored procedure that returns a list of key fields for all customers, your stored procedure script might look like this: CREATE PROCEDURE dbo.CustomersRetrieveList_sp
AS
SELECT
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address
FROM Customers WITH (NOLOCK)
There is no parameter in this case because all rows are to be retrieved and included in the list. If instead of deleting rows you add a status field to your table to mark rows as active or deleted, then you will need to add a WHERE clause to only retrieve those rows that have an active status. This article contains the code to generate both of these types of retrieve stored procedures. You may want to add more types, such as insert, update, and delete stored procedures. If so, you should also define the structure for those types of procedures. Common practice is to create one stored procedure that supports insert, update, and delete operations passing in the ADO RowState as a parameter to determine which operation to perform. | & | | 
By: Deborah Kurata
Deborah Kurata is cofounder of InStep Technologies Inc., a professional consulting firm that focuses on turning your business vision into reality using Microsoft .NET technologies. She has over 15 years of experience in architecting, designing and developing successful applications.
Deborah is the author of several books, including Best Kept Secrets in .NET (Apress), Doing Objects in Visual Basic 6.0 (SAMS) and Doing Web Development: Client-Side Techniques (Apress). She is on the INETA Speaker’s Bureau, is a well-known speaker at technical conferences, and is a Microsoft Most Valuable Professional (MVP). After a hard day of coding and taking care of her family, Deborah enjoys blowing stuff up (on her XBox of course).
Some of the information in this article was obtained from her upcoming book, Doing Objects in VB 2005.
deborahk@insteptech.com | Fast Facts | | You can use the Microsoft Data Access Blocks (part of the Enterprise Library) to retrieve data for any application. See www.microsoft.com/practices for more information. | |
|