Next, the code has to determine whether the object with the requested name exists and make sure the object is a table. The query at callout D accomplishes this task by retrieving the object type from the INFORMATION_SCHEMA.TABLES view. The code uses the system function Object_ID with @objectname as a parameter to determine whether the object ID exists (i.e., is not null). If the object ID doesn't exist, the code issues the error message "OBJECT @objectname DOES NOT EXIST." Similarly, the code at callout E references the system function Object_ID to determine whether @objectname is a table. If @objectname isn't a table, the code generates the error message "OBJECT @objectname IS NOT A TABLE."
In the next few steps, the code extracts column information from the INFORMATION_SCHEMA.COLUMNS view. To begin this process, the code at callout F creates a temporary table, #TB_SCHEMA, to keep this column information. The code scans this table three times to build a parameter list, a SET statement, and search arguments. Table 1 shows the structure of this temporary table.
Now, the code at callout G populates the #TB_SCHEMA table by using a SELECT statement. Note that the column PK_Flag is initialized to 0. The UPDATE statement that callout H shows sets PK_Flag to 1 for all primary key columns in the #TB_SCHEMA table. The code identifies primary key columns by querying two views: INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and INFORMATION_SCHEMA.TABLE_CONSTRAINTS. Let's examine these two views. INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE contains constraint names and cross-referenced column names. INFORMATION_SCHEMA.TABLE_CONSTRAINTS contains information about constraint properties, including constraint type. By joining these two views to the #TB_SCHEMA table, you can identify which columns of #TB_SCHEMA belong to the primary key constraint.
The code at callout I determines whether the primary key constraint exists. If the constraint doesn't exist, the code issues the error message "Object "table_name" DOES NOT HAVE A PRIMARY KEY" The next step is to remove the columns that the code can't reference in the UPDATE statement, as the code at callout J shows. These column data types are text, ntext, image, timestamp, rowversion, and the identity column, which isn't a primary key constraint. Note that the code can reference an identity column in an UPDATE statement as a search argument, but not in a SET clause. At this point, the #TB_SCHEMA table contains all the required information for the code-generation process. In the CREATE PROCEDURE T-SQL statement that callout K shows, the generated stored procedure name is based on the table name. First, the code drops the stored procedure if it already exists. If the table name contains spaces, the code replaces those spaces with an underscore character.
Next, the code at callout L, page 36, generates the parameter list. To obtain data for the parameter list, the code extracts data from the #TB_SCHEMA temporary table it built earlier. Each row in this table corresponds to a separate parameter. You can use one of two methods to perform row-by-row processing. One approach is to declare a cursor. The second approach, which we believe is more elegant and efficient, is to generate columns that contain unique values.
As you've probably noticed, the temporary table has an identity column called RowNo. To generate those unique-value columns, callout L's code first uses the MAX() and MIN() functions to retrieve the maximum and minimum values of the RowNo column. These values will become the last and first rows in the temporary table. Then, the code retrieves row data by using the @RowNo value it just obtained for that row as a key. The code starts building the parameter list based on the data type of each column involved. The code uses the @pRowNo variable to store the current row it's processing. The final SELECT statement in callout L's code retrieves @RowNo using the MIN() function again and the current @RowNo value as the search argument where the next row number is greater than the current row number. This section of code also checks that it isn't processing the last row, and if it isn't, the code appends a comma to each line to separate the items in the list. The code continues to process the BEGIN...END block until it has no more rows to retrieve. The final steps in callout L's code generate the definition for @ERRORCODE and generate a comment line showing where to put any additional validation logic you need.
The block of code at callout M builds the UPDATE statement. Building this statement requires traversing the #TB_SCHEMA table one more time to build the WHERE clause and SET list. To build the WHERE clause, the code uses the primary key constraint as a search argument. If the column that you're updating belongs to the primary key constraint, the code appends to the WHERE clause a string in the format <column name> = @<column name>; if the column doesn't belong to the primary key constraint, the code prints the string as a part of the SET clause.
The final section of Listing 1, which callout N shows, is for error handling. This basic code contains one interesting feature: the use of the OBJECT_NAME() system function. Passing @@PROCID as a parameter to this function will return the name of the stored procedure that Listing 1 created. To provide a fully descriptive error message while still keeping the code generic, you need to display the stored procedure's name in the error message. Note that you can easily modify the error-handling code and the WHERE clause to meet your application's requirements.
Using the Application
Now, you're ready to use the application in Listing 1 to create a stored procedure that updates a table. To use the application against the Pubs database, you perform six simple steps. First, launch Query Analyzer and paste Listing 1's T-SQL code into the query window. Click the Execute Query icon or type F5 or Ctrl-E to run the code. Next, select the commented text sp_CreateUpdateProc 'authors'and right-click to execute the stored procedure that the code generates. Then, copy the results of the sp_CreateUpdateProc stored procedure from the output window into a new query window and execute the stored procedure. Under the Pubs tree view, view the stored procedures that are in Pubs and press F5 to refresh the view. The screen will display the generated dbo_p_Update_Authors stored procedure. You can now execute the generated stored procedure to perform the updates you require.
Using this T-SQL application to create stored procedures can save you a lot of time and eliminate errors when you're updating, inserting, or deleting rows in a table that has many columns. An added advantage is that when you use the generated stored procedure, SQL Server can reuse the execution plan and thus speed up your queries' execution time.
End of Article
Prev. page
1
[2]
next page -->
