Rusty Divine

Live, Love, Learn, Teach

Dynamically Generated SQL Stored Procedures

One of my favorite MSDN articles has been the key to saving countless tedious hours manually creating the select, insert, update, and delete stored procedures in SQL Server 2000 for web applications: Peter W. DeBetta and J. Byer Hill, MSDN April 2003, Automate the Generation of Stored Procedures for Your Database.

Some developers prefer not to use stored procedures for various reasons, but I agree with Douglas Reilly who essentially concludes that if you don't need to worry about switching from SQL Server to another RDBMS, and if some of your procs have complicated processing in them, then generally it is advantageous to use SQL stored procedures over ad hoc SQL.

The SQL script from the MSDN article creates a few functions that query the sys tables for schema information, and stored procedures, one each for generating select/insert/update/delete procs that take a table name as a parameter. For example, executing the proc that selects a record in a table generates and executes the following script (example based on the Northwind Customer table):


EXEC @RC = [Northwind].[dbo].[procMakeSelectRecord] 'Customers'

Yields:


IF EXISTS(SELECT * FROM sysobjects WHERE name = 'procSelectCustomers')
DROP PROC procSelectCustomers

------------------------------------------
-- Select a single record from Customers
------------------------------------------
CREATE PROC procSelectCustomers
@pstrCustomerID nchar(10)
AS

SELECT CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
FROM dbo.Customers
WHERE CustomerID = @pstrCustomerID

Notice how the script added the where clause based on the primary key in the Customers table without having to specify that in the execution statement. The script also added a comment, and formatted the new proc in a standard way, including the proc name, "procSelectCustomers". Now, if I wanted to go in and add an Order By clause, it would be easy to alter and customize it. (Note: The formatting applied to this post removed all indentation for the code; it is possible to generate procs that use indentation)

The benefits of using the tool include:

  1. Easy standardization on naming conventions and formatting, which promotes readability - just customize the script to suit your needs. Imagine all of the procs in the database with standard indentation, line breaks, and the required meta data all automatically generated!
  2. Saves time and avoids errors from typing out simple procs, and it is a good first step on complicated procs that need some more processing.
  3. Promotes organization by naming all the procs in the same format, which will make it easy later to find the procs that act on a certain table, and will make it easier for new developers to the project to get up to speed.

If your project is destined for SQL Server, and you agree that stored procedures are the way to go, then try customizing the MSDN script to fit your standards. You can reap the benefits of increased efficiency in authoring, time saved reacquainting with the code at a future date, and getting new team members up to speed quickly.

One final tip: if the installation script is executed on the Model database, then every new database will likewise have the ability to generate procs based on its database tables. Since you will likely want to customize the naming conventions and formatting used, however; I would recommend running the script on a test database first.

blog comments powered by Disqus