Think about the last time you had to update a table that contained a large number of columns. Now, imagine that the table is under construction and you're responsible for adding or deleting columns or modifying columns' data specifications. And, of course, this situation occurs when you're on a tight schedule and have other, more important problems to solve—what a headache! But you can automate a solution to this problem by combining the basic capabilities of SQL Server stored procedures with SQL Server's underlying data dictionary.
A stored procedure is a group of T-SQL statements compiled into one execution plan. Using stored procedures has several benefits. First, stored procedures let a developer efficiently reuse code. Once you create a stored procedure and store it in the database, anyone who has access to it can call the procedure any number of times, so you don't have to keep rewriting the same piece of code. The result is more productive use of a programmer's time. Second, stored procedures enable faster query execution. Because a stored procedure is precompiled and SQL Server reuses the resulting execution plan, when you use stored procedures, you get significant savings in time and overall CPU utilization. If an application repeatedly calls the same stored procedures, the application will retain those stored procedures in memory, thus saving additional time—especially when compared to the time required to call the equivalent T-SQL code many times. Third, using stored procedures reduces overall network traffic. Instead of transmitting many lines of T-SQL code, SQL Server transmits only the name of the stored procedure it's calling, thus optimizing the use of network bandwidth. Finally, using stored procedures enhances security controls. Even if users don't have permissions to access specific tables, you can grant them permissions to execute a stored procedure that references those tables. This method of limiting table access can save administrative effort.
Even though the benefits of using stored procedures are valuable, you still have to expend time and effort to write the procedures. Writing stored procedures for UPDATE, INSERT, and DELETE operations is time-consuming, and the process is full of opportunities to introduce errors. For example, a typical stored procedure for an UPDATE operation contains several parts: a CREATE PROCEDURE statement; a parameter list, which can be lengthy, depending on the number of columns in the table; an UPDATE statement; a SET list, which identifies the list of columns the stored procedure will update; and a WHERE clause to list the criteria that specify which rows the procedure will update. Similarly, a stored procedure for inserting rows into a table consists of the following parts: a CREATE PROCEDURE statement; a parameter list; an INSERT statement; a list of columns that identifies the columns the procedure will insert; and a VALUES clause to list the criteria that specify which rows the procedure will insert. Creating a stored procedure for deleting rows from a table is relatively simple: It requires only selection criteria as parameters and a WHERE clause to limit the number of rows the procedure will delete.
Now, think about the time you could save by automatically generating parameter and SET lists that contain all the information required for each column. You can make this automatic generation happen by taking advantage of the data dictionary or metadata that SQL Server stores in information schema views in the master database.
Information Schema Views
A database's information schema view contains metadata for all data objects in that database. In SQL Server 7.0, Microsoft added information schema views to the list of system objects and thus made SQL Server compliant with the ANSI SQL-92 standard. Figure 1 shows a list of the SQL Server 7.0 information schema views. These views, which are available in the master database only, are the ones that we refer to in this article. The COLUMNS view contains information about table columns and data types. The CONSTRAINT_COLUMN_USAGE and TABLE_CONSTRAINTS views contain information about constraints, constraint type, and the columns involved in a particular constraint. The constraint we're interested in is a primary key constraint. The primary key constraint columns require building the WHERE clause for UPDATE and DELETE stored procedures.
We make two important assumptions in the program that Listing 1, page 34, shows, which we use to generate stored procedures. First, we define the stored procedure name as p_<Function>_<Table Name> where the function is UPDATE, INSERT, or DELETE. If the table name contains a space, the code will replace the space with an underscore character (_). In the example code, a space in a table name such as My Table causes problems when you already have a table called My_Table because you end up with two tables that have the same name. Second, the code defines the parameter name as a concatenation of the at character (@) and column name; the code replaces spaces in the column name with underscore characters.
Walking Through the Code
The solution that Listing 1 shows generates a stored procedure that updates a table. To create a stored procedure that performs an INSERT or DELETE against the table, you use the same basic logic. You can download code to create all three types of stored procedures at http://www.sqlmag.com, InstantDoc ID 43672.
The T-SQL code at callout A in Listing 1 begins by determining whether the stored procedure we're creating already exists. If the stored procedure already exists, the code drops it. The code uses the CREATE PROCEDURE command at callout B to generate the sp_CreateUpdateProc stored procedure, which contains two parameters: the table name and the table's owner. The name of the stored procedure and the parameters it passes are commented so that you can select this commented text with a mouse rather than rekeying it, as we explain later. The block of code at callout C declares all required variables and concludes by setting the table owner to DBO if the owner was null.
Prev. page
[1]
2
next page 
