e 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.

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. See the sidebar More On Stored Procedures for useful links to background information on stored procedures.

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 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

A stored procedure generator is basically an application that, when run, creates a stored procedure script.

This example uses a five-character string for the primary key, but best practices often dictate 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 data-retrieval stored procedure collects all the key values for all the rows in the table. You can 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)

This case requires no parameter because it retrieves all the rows and includes them 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 retrieve only those rows that have an active status.

This article contains the code to generate both of these common 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 value as a parameter that determines which operation to perform.