Automatic Generation of stored procedures in Databases

Msdn magazine megal 2003 (Advanced T-SQL)

Download the original code: storedprocedures.exe (0000kb)

This document assumes that you are familiar with T-SQL.
　

Summary

The automation during design accelerates coding and ensures that all processes are generated using the same naming rules and structure. In order to maximize coding efficiency in large SQL projects, the author has compiled a series of design-time stored procedures to use them to generate Run-Time) stored Procedures, and has been used in projects. Recently, the author updated his stored procedures to use SQL Server 2000 features, including user-defined functions. This article covers creating and executing these dynamic T-SQL scripts to automate common database stored procedure coding.
　

Previously, we developed a fairly large N-layer Client/Server project. During the initial plan, we decided to use a series of methods to access a large scale in the database. There are four basic stored procedures used to perform select, insert, uodate, and delete operations on each table. Although the storage process is similar in design, the structure of the unique index column in each table controls the important details of each independent process, which will undoubtedly lead to monotonous compilation of the storage process. Obviously, the writing process of the stored procedure must be automated.
In any given project, start from the experiment to establish a set of core design-time stored procedures, and use them to compile the basic runtime stored procedure set of database tables. By creating and using these design processes, we not only save a lot of time, but also save money for our customers, and also leave some good code that we are still using.
These stored procedures have been updated to take advantage of some new features of SQL Server 2000, especially udfs. As a result, the Code becomes very modular and we have additional functions to complete other tasks.
Another advantage of automation during design is to ensure that hundreds of stored procedures have consistent structures and standard naming rules. In our example, the names of all generated runtime stored procedures are formatted as prapp_tablename_task. Here, the task can be select, update, or delete. The stored procedure for the customers and orders tables is as follows:

　　prApp_Customers_Delete
　　prApp_Customers_Insert
　　prApp_Customers_Select
　　prApp_Customers_Update
　　prApp_Orders_Delete
　　prApp_Orders_Insert
　　prApp_Orders_Select
　　prApp_Orders_Update

As you can see, this specification adds a large number of organizations to the database, so that any stored procedure can be easily located and the name of each process is self-described. Developers can quickly discover it and create code. Most importantly, future TEAM members of this project will find that the code and stored procedures are easy to understand and get started. Of course, if you have already used a different naming convention, you only need to change a few lines of code to replace your naming convention.
These four stored procedures are not static, but can be used as templates in other projects. Install them in the project database. If necessary, you can modify them to meet the needs of specific applications. For example, in several of our applications, we add code to maintain the audit records modified each time in a separate database.

A simple example 　　　　　　　　 　　　　　　

First, let's take a look at a simple example of using the order_details table in the database northwind (the table name is modified and the space character is replaced by an underscore ). Although spaces and other characters are allowed in object names, we recommend that you use regular delimiters to name objects to prevent problems that may occur when using these automated stored procedures, for more information, see "using identifiers" in SQL Server online books.
The first task is to run this design-time stored procedure to create a stored procedure for modifying the data in the order_details table:

　　EXEC pr__SYS_MakeUpdateRecordProc ''Order_Details''

Running this design-time stored procedure will generate a T-SQL script as shown in Figure 1. When this T-SQL script runs, it creates a new update stored procedure for the Order_Details table. All columns are described as parameters of the new stored procedure, but note that when its non-primary key field is part of the SET statement of the update command, the primary key column (OrderID and ProductID) how the WHERE clause appears. During design, the stored procedure checks the metadata (metadata) stored in the Order_Details table in the SQL Server system table, and uses this information to create an appropriate output script. After running, the final stored procedure at runtime is created.
Only output is generated after running, and no new runtime stored procedure is generated. However, with a simple modification, the stored procedure can actually run the T-SQL script as the final output result at design time. To this end, we only run the stored procedure during design. We use value 1 as a flag, pass it with the second selectable parameter, and run it again:

　　EXEC pr__SYS_MakeUpdateRecordProc ''Order_Details'', 1

This not only displays the previous output results, but also runs the output results to create a runtime stored procedure.

Now let's take a look at the Stored Procedure Code for the design of the stored procedure when a specific application is created.

SQL Server System tables and views　　　　 　　　　　　　　 　　　　　　

To create a stored procedure during design, we must know how to get table definitions from the SQL Server system table and Information view. First, we must find these columns and find out which are the primary keys, the data types supported by each column, and whether the columns can be empty.

Figure 2 view system table

SQL Server Enterprise Manager can help you view system tables by modifying the attributes of the registration Server, as shown in Figure 2. If you right-click the server name in Enterprise Manager and select"Edit SQL Server Registration properties",A dialog box is displayed. In the dialog box, you can see a check box marked with "Show system databases and system objects. Select this option to open the system object view. You can also disable it to make the view of the table simpler and easier to read.

Parse table columns

The Syscolumns table provides many required metadata information, such as the column name, ID, length, and whether to allow null values. It is also used to connect the sysindexes table to determine the table's primary key. You can also use the INFORMATION_SCHEMA.COLUMNS view to obtain the column default value.
Since all stored procedures use the same metadata information, it is good to encapsulate it in an independent code block for modularity and maintainability. In earlier versions of SQL Server, udfs (User-Defined Functions) were not available, making modularity difficult. However, SQL Server 2000 has the UDF feature. We decided to further adopt the code and modularize the public features in the four stored procedures during design. Create five new UDFs to process system tables and information outline views and encapsulate all acquired metadata.
Undoubtedly, to create a new runtime stored procedure, we need to know the following metadata column information about the table:

• Column name
• Column ID
• Column data type
• Maximum column length (including characters and binary data)
• Column precision or the number of digits of the value (decimal and numeric data)
• The value range of the column, or the number of digits after the decimal point (decimal and numeric data)
• Whether the column is allowed to be null
• Whether the column is part of the primary key
• Whether the column is an Identity column
• Column Default Value

Most of the information is from syscolumns tables, except for two. The default value is actually from the INFORMATION_SCHEMA.COLUMNS view. The data type name (datatype name) is learned from the policypes table and determined whether a column is part of the primary key through a more complex syscolumns, sysindexes, and sysindexkeys table combination. It is so complicated that we encapsulate this function into our own UDF.
Let's take a look at the main functions in Figure 3, which reveal more metadata information. This UDF is not too complicated. As you can see, most of the metadata information-except some simple column renaming-is not modified when the returned data is returned, including column name, column ID, length, precision, range, whether to allow null, and data type name. Next, we need to do some additional work on this information. For primary key metadata, we have created another UDF to determine whether a column in the table is part of a primary key of a table. We will immediately check these additional UDFs functions.
Let's take a look at the alternate type and identity status. The 8th-bit (128) value of the Syscolumns Status field indicates whether the column is an identity column. (This is important to know when to create Insert and Update scripts ). Our simple formula implements a logic and (&) for this value, and encapsulates the result in the Sign function. If this bit is set, it indicates that this column is the identity column. C. status & 128 returns 128. Otherwise, the return value is 0. The Sign function returns 1 if it is a positive value and 0 if it is a negative value. Therefore, if the column is evaluated as the identity column, 1 is returned; otherwise, 0 is returned.
The alternate type is used to indicate whether the data type requires additional information (length, accuracy, or range) during definition ). The character and binary data types are used as the alterate type whose values are 1, decimals and numerics as 2, and their data types are 0. The Stored Procedure determines whether the length, precision, and range need to be added to the parameter definition.

Search for primary key columns

As you can see, it is not very difficult to find the column information. It takes a little effort to check whether a field is a part of the primary key. You can obtain a list of fields. However, to find these fields, you must compare them in the syscolumns, sysindexes, and sysindexkeys tables with the columns we requested (the @ sColumnName parameter is passed to the UDF ). Therefore, it is easier to find the primary key in a single user-defined function, because we can encapsulate this work into a single function call.

Let's take a look at this function to see the truth:

CREATE FUNCTION dbo.fnIsColumnPrimaryKey
    (@sTableName varchar(128), @sColumnName varchar(128))
RETURNS bit
AS
BEGIN
    DECLARE    @nTableID int,
               @nIndexID int,
               @i int

    SET @nTableID = OBJECT_ID(@sTableName)

This function contains two parameters, table name and column name. If the specified column is part of the primary key of the table, the function returns a bit flag. We then declare the variables used in the stored procedure and assign the initial values. Now let's look for the primary key information. We start to find the index ID for the table's primary key index, as shown in the following code:

SELECT @nIndexID = indid
FROM      sysindexes
WHERE  id = @nTableID
 AND      indid BETWEEN 1 And 254 
 AND      (status & 2048) = 2048
ORDER BY indid

IF (@nIndexID  Is Null)
    RETURN 0

Now, assign the index ID of the primary key index of the table to the variable @ nIndexID. The 12th-bit (2048) of the Status column indicates whether it is a primary key index. If it is not a primary key, no record is returned and @ nIndexID is set to a null value. If @ nIndexID contains a null value when exiting the function, 0 is returned. In other words, if there is no primary key index, the column is not a part of the primary key. Now we will check the required column (@ sColumnName) in the primary key index column list again ).

    IF @ColumnName IN
        (SELECT sc.[name]
          FROM       sysindexkeys sik
          INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid =
                     sc.colid
          WHERE      sik.id = @nTableID
          AND        sik.indid = @nIndexID        )
     BEGIN
        RETURN 1
     END

    RETURN 0
END

Using the obtained IndexID, we obtain the column name from the Union of syscolumns and sysindexkeys. These tables are combined by column ID and Object ID. You can use the WHERE clause to set search conditions. Therefore, you can select only the index columns (sik. id = @ nTableID), and only select the primary key index (sik. indid = @ nIndexID ). If @ sColumnName is in the list of returned columns, return value 1. Otherwise, return value 0. The ID match is not found.

Column Default Value

When a record is inserted into a table, if the value is not provided for a specified column and the column has a default value, the default value will be used as the value of the column. Because a new table is inserted into the stored procedure, a parameter is used for all columns that may be inserted, and the variable must contain a value, even if it is a null value, the default value of the table will not be used. Essentially, by explicitly providing a value (even NULL) for each column, we rewrite the default value of the column. To match the stored procedure we created, we must provide the default value when inserting data. After this article, we will see how to use default values in automated stored procedures. But now, let's first look at how to get those default values.
The UDF we will use simply references the INFORMATION_SCHEMA.COLUMNS view, which provides the default value of a column. Compared with sysconstraints system tables, it is easier to use this view to obtain the default value. The next UDF encapsulates the default search logic into a simple function call to simplify this process.

CREATE FUNCTION dbo.fnColumnDefault(@sTableName varchar(128), 
                                    @sColumnName varchar(128))
RETURNS varchar(4000)
AS
BEGIN
    DECLARE @sDefaultValue varchar(4000)

    SELECT  @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
      FROM      INFORMATION_SCHEMA.COLUMNS
      WHERE     TABLE_NAME = @sTableName
      AND       COLUMN_NAME = @sColumnName

    RETURN  @sDefaultValue

END

The column default value is stored in a pair of parentheses, but we do not need. As you can see, we pass the COLUMN_DEFAULT field to another function fnCleanDefaultValue, which removes the square brackets and returns the actual default value.
For example, if a column named nQty has a default value of 1, The COLUMN_DEFAULT value includes (1) Of course ). If the default value is "Enter Text Here", we will get it ("Enter Text Here "). Here is the source code of this UDF:

CREATE FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
    RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)
END

Now we get all the metadata required to create an automated stored procedure.

Dynamic execution T-SQL

The execution of dynamic T-SQL is the essence of our stored procedure, which allows you to write a general T-SQL script that generates T-SQL scripts in order. It is exactly the T-SQL's EXECUTE Command that allows generic T-SQL scripts to specifically EXECUTE specialized output and create runtime stored procedures that will be used by the application.
EXECUTE or EXEC has two functions: EXECUTE an existing stored procedure and dynamically EXECUTE an SQL command stored in a string. This is exactly the next function. We will use the Union of obtained metadata to automatically create these stored procedures. A simplified view of this process will fill in a large varchar variable with the Stored Procedure Code (using metadata) to create the stored procedure, and then dynamically execute the content of this varchar variable at one time, create a new stored procedure.
Let's start with a simple example of testing a dynamic T-SQL:

CREATE PROC prGetAuthor
    @au_id char(11)
AS
DECLARE @sExec varchar(8000)
SET @sExec = ''SELECT * FROM authors WHERE au_id = '''''' + @au_id + ''''''''

EXEC (@sExec)

In this example, we pass in the author ID and connect it to a SELECT statement that gets an author from the author table.

We call this stored procedure as follows:

　　EXEC prGetAuthor ''123-45-6789''

The prGetAuthor stored procedure creates the following SQL statement:

　　SELECT * FROM authors WHERE au_id = ''123-45-6789''

This statement is executed in EXEC and returns author with ID 123-45-6789. As you can see, the stored procedure will use this feature at a very high level during design.
But we should note that this is not the use of dynamic T-SQL recommendations. Dynamic T-SQL code is accessible to the outside world at any time, so there is a possibility of SQL attacks. We only use dynamic T-SQ L for management and task purposes and will never expose this feature in any stored procedure accessible to anyone except system staff and managers.

Create a stored procedure

The first step in creating these design stored procedures is fairly standard. Define the stored procedure, declare the variable, and initialize the variable. Before creating other stored procedures, you can quickly check whether the code is suspicious. We create two special string variables, one saving the TAB character and the other saving the carriage return line break. These can be created using UDFs, but we decided not to do so that the exercises can be left to the readers. They are used to assist in formatting code output. Let's take a look at the beginning of this process, as shown in Figure 4.
Again, there is no new discovery of T-SQL here. Check whether a primary key exists. This will prevent our code from creating potentially dangerous runtime stored procedures. Then set some variables and default values. The stored procedure first creates a DROP statement for the new procedure to avoid the existence of the stored procedure, and then creates some annotations to create the actual stored procedure definition (see the first few rows in Figure 1 ). You can modify the code to create a non-existing runtime Stored Procedure (if it exists, nothing will be done ). This new feature will be set by the third option parameter @ bIfExistsDoNothing. We will leave it to readers as a simple exercise.
The next code snippet begins the process of creating a dynamic T-SQL. To define a new stored procedure (see Figure 5), add and delete an existing Stored Procedure and Code for defining the new stored procedure. Note how to use the second parameter (optional) @ bExecute to determine whether we need to actually run the code. In our definition of automated stored procedures, this parameter is optional. The default value is 0, meaning that the Code is not actually executed.
Next we will use an interesting feature. We use the fntablecolumninfo user-defined function as the metadata of the cursor. fntablecolumninfo is a function that returns the table value. This function is used to replace the complex T-SQL by referencing this UDF only in the declaration of the cursor in four automated stored procedures. After the cursor is declared, open it and obtain the first record, put it in a variable containing metadata information, we can use it to create our new stored procedure (see figure 6 ).
Of course, we use the while statement to establish a loop until all values are obtained (@ fetch_status = 0 ). Now we are going to parse the column information and create key statement segments for the new stored procedure.
In the next code example, we use cursors to loop and use column metadata to create code. You will notice that three variables have been modified: @ skeyfields, @ ssetclause, and @ swhereclause. The first one is used to create a parameter list for the stored procedure (including the create PRDC segment in Figure 1 ). The second is the set statement used to set the update command in Figure 1. The last variable is used to set the WHERE clause at the end of Figure 1. Now let's verify the first part of the code (see figure 7 ).
Figure 7 contains the code for creating a parameter list for a new stored procedure. The first if statement checks whether the data is ready to be added to the variable. If you are ready, add a comma and a carriage return/line feed. We must end each parameter in the parameter list correctly. If no check is performed, we end with one or more commas. This error can be prevented by adding a comma before the next column.
Next, we add a character and metadata concatenation, including a Tab character, a @ character, column name, a space, and column type name. Next, we will check whether other information of the data type is required, and whether information such as precision, range, and length is required. If needed, we also add these values in parentheses (as required by the T-SQL syntax ).
Finally, if the column is not an identity column, and the column allows null values or a timestamp (update is not allowed because it is directly automatically updated ), then we add "= NULL" to the parameter definition ". For example, the columns in the discounts table in the database pubs are as follows:

　　discounttype varchar(40),
　　stor_id char(4) = NULL,
　　lowqty smallint = NULL,
　　highqty smallint = NULL,
　　discount decimal(4, 2)

Note that the discounts table does not have a primary key and code generation is not allowed. These stored procedures rely on primary keys to determine how data is updated. If there is no primary key, this automated stored procedure should be modified, use all columns in the WHERE statement of the new stored procedure, or find a unique index column for The WHERE clause. In other words, if possible, all tables should have primary keys, which is the basic principle of database design.
Next, let's take a look at the code of the SET statement created for the UPDATE command of the new stored procedure (see Figure 8 ). Note how to handle columns that are not primary keys. Again, IF you want to update all columns, including the columns in the primary key, you can simply delete the IF statement. Note that this IF option can be set by another parameter. In the last part, add a comma for the variable if needed. In this example, if there is no data (meaning we haven't added any columns), we will SET the SET statement in the variable to close it.
Next, we add a TAB character, the column name to be updated, and an equal sign (= ). In the Order_Details table, the following code ends:

　　　SET　UnitPrice = @UnitPrice,
　　　　　 Quantity = @Quantity,
　　　　　 Discount = @Discount

Next, create the WHERE statement for the new stored procedure. You will notice that the code segment has an ELSE statement. This is the exceptional state of primary key check, indicating that this column is part of the primary key and only runs this code (see Figure 9 ).
Again, this variable may start with the WHERE clause, or add an AND clause, which determines whether it is the first item of the WHERE clause. Next, we add a TAB character, column name, string "= @" and column name. The result of the Order_Details example is shown below:

　　WHERE OrderID = @OrderID
　　　　 AND ProductID = @ProductID

Before the WHILE loop ends, we need to get the next row from the cursor and place the metadata value in the variable again. Once the cycle ends, we close and clear the cursor. Now we can output information about any newly created runtime stored procedures (see Figure 10)
Finally, the stored procedure will output the T-SQL of the new runtime stored procedure, first add a carriage return line break to the SET Statement (purely for the purpose of format output ). Next, we add the keyword field (stored procedure parameters) and the keyword AS (stored procedure definition requirements ). Then, UPDATE and add the updated table name. Finally, we add the SET statement variables and WHERE statement variables to end the definition of the stored procedure. Note that the @ sProcText variable includes the T-SQL for the new runtime stored procedure, which you can choose to execute. If executed, the stored procedure will be added to the database.

Conclusion

The above is only one of the four automated stored procedures we have developed. Of course, each stored procedure will change as needed. For example, when creating a run-time delete stored procedure, the stored procedure only uses the primary key of each table. All user-defined functions and stored procedures can be downloaded through the link above this article.
These stored procedures can add many other features, some of which we have mentioned, such as using the same object name, object existence verification, and creating audit indexes, and change the statement when the stored procedure exists (it is more effective to maintain the stored procedure and hybrid XML ). You can also create additional settings in a table to help with code generation. In other words, these stored procedures can serve as the starting point for its Automatic Code Generation tasks. In other examples, this code will help you save a lot of time and effort and even help you explore other T-SQL technologies of interest.

Related Articles 

. Net reflection: dynamically bind your data layer to stored procedures and SQL commands using. Net metadata and reflection

SQL and XML: use XML to invoke and return stored procedures over the web
Serving the Web: Stored Procedure wizard in Visual Basic boosts productivity

Background information
　　　　　　　　
Inside Microsoft SQL Server 2000, by Kalen Delaney (Microsoft Press, 2000)
The Guru's Guide to SQL Server Stored Procedures, XML, and HTML by Ken Henderson (Addison-Wesley Professional, 2001)

Introduction

Peter W. debetta:He is a Wintellect trainer. He uses Visual Basic, ASP,. NET Framework, and SQL Server to solve and develop enterprise-level software. He has also co-authored some books, including SQL Server 7.0 Programming Unleashed (SAMS, 1999 ).

J. BYER Hill:Has its own company, Advanced Software Logic. Byer has 11 years of experience in writing and designing databases using Microsoft technologies such as Visual Basic, Visual C ++, COM +, ADO, ASP, DHTML, and SQL Serve. His main focus is on the construction, design, and efficient code reuse of application systems.

Introduction to translators
　　　　　　　　
Xiao Jin: Nanjing zhongcui Food Co., Ltd. Information Department, software engineer.