Automation generation of stored procedures in the database (reproduced)

zhaozj2021-02-16 152

Original source:

MSDN Magazine April 2003 (Advanced T-SQL)

Original code download:

StoredProcedures.exe (108KB)

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

Summary

Automation at design speeds up the encoding and ensures that all processes are generated with the same naming specification and structure. In order to improve the efficiency of coding in large SQL projects, the author has written a series of design (Design-Time) stored procedures, use them to generate runtime storage procedures, and have been used in engineering. Recently, the author updates its stored procedure to use the features of SQL Server 2000, including user-defined functions. This article and content cover the creation and execute these dynamic T-SQL scripts to automate the ordinary data fixtures.

Previously, we have developed a fairly large N-layer client / server project. During the initial plan, we decided to use a large number of methods in the database. There are four basic stored processes to implement SELECT, INSERT, UODATE, and DELETE operations for each table. Although the desired stored procedures are designed, the unique cable of each table is different, which will have different important details of each independent process, which will undoubtedly lead to the preparation of stored procedures very monotonous. Obviously, the preparation process of stored procedures must be automated. In any given item, from the experiment, establish a set of core design stored procedures, use it to write the basic runtime storage procedure set of database tables. By creating and using these design, we not only save a lot of time, but also save money for our customers, and there are some good code that we are still in use. The stored procedures have been updated in these design to utilize some new features of SQL Server 2000, especially the user-defined function (UDFS) feature. Therefore, the code becomes very modular, and we have additional functions to complete other tasks. Another design time is the benefit of ensuring that hundreds of stored procedures generated have consistent structure and standard naming specifications. In our example, all the names of all the runtime stored procedures are formatted as: Prapp_Tablename_Task, here Task can be SELECT, UPDATE, or DELETE. The stored procedures for the Customers and the Orders table are 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 lot of organization to the database, making any stored procedure easily positioned and enables the names of each process to be described. Developers can quickly discover it and create code. The most important thing is that the future team members will find that these codes and store processes are easy to understand and get started. Of course, if you have used a different naming specification, you only need to simply change the few lines of code, your naming specification can be replaced. The stored procedure is not always constant, but can be used as a template in other items. Install them into the project database, if needed, you can modify their need to adapt to a particular application. For example, in several applications, we increase the code to maintain the recorded audit record per recorder in a separate database. ?

A simple example?

Before starting, first look at the simple example of using the ORDER_DETAILS table in the database Northwind (the table name of the table is modified, and the space character is replaced with the underline). Although spaces and other characters are allowed in the object name, we recommend using regular split to name objects to prevent problems that may occur when using these automated stored procedures, see the "Using Identifiers" section in SQL Server online books Come get more information. The first task is to run this design stored procedure to create runtime stored procedures for modifying Order_Details table data: EXEC PR__SYS_MAKEUPDATERECORDPROC '' ORDER_DETAILS ''

The stored procedure will produce the T-SQL script as shown in Figure 1. When this T-SQL script is run, it creates a new Update stored procedure for the Order_Details table. All columns are illustrated as parameters for new storage procedures, but notice how the primary key column (ORDERID and ProductID) appear in the WHERE field when other non-keysfields are part of the set statement of the Update command. Design time stored procedure Check the order_details table stores metadata in the SQL Server system table and uses this information to create an appropriate output script, and will create the final runtime stored procedure after running. There is only outputs after running, and there is no new runtime stored procedure. However, make a simple modification, the stored procedure actually runs the T-SQL script as the last output result. To do this, we only run the design stored procedure, use the value 1 as a flag, passed the second alternative parameter, and run again:

EXEC PR__SYS_MAKEUPDATERECORDPROC '' ORDER_DETAILS '', 1

This not only shows the previous output results, and runs this output result, thereby creating a runtime stored procedure. Now let's take a look at this code when the design of the design of the stored procedure is created. ?

SQL Server system table and view?

In order to create a design stored procedure, we must know how to get the definition of the table from the system table and information view of SQL Server. First, we must find these columns and find what is primary key, which data types are supported, and whether columns are allowed to be empty. ?

Figure 2 Check the system table?

Understand the properties of the registration server, SQL Server Enterprise Manager allows you to view the system table is very useful, as shown in Figure 2. If you right-click the server name in the Enterprise Manager and select "Edit SQL Server Registration Properties", a dialog will pop up. 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 choose to turn it off to make the view look simpler and easier to read.

Analyze the table column?

The SysColumns table provides a number of metadata information, such as column name, ID, length, and whether to allow null values. It is also used to connect the sysindexes table to determine the primary key of the table. The default value of the column can also be obtained by the information_schema.columns view. Since all stored procedures use the same metadata information, it is a very good thing for packaging in a separate code block for modularity and maintainability. The early version of SQL Server does not have UDF (user-defined functions), making modular looks difficult. However, SQL Server 2000 has a UDF feature, we decided to further use the code and modularize the public characteristics of four design stored procedures. Create five new UDFS to handle system tables and information outline views, package all obtained metadata. There is no doubt that in order to create a new runtime stored procedure, we need to know the following metadata column information about the table: The maximum length of the data type column of the column column (including characters and binary data) columns , Or the value of the number of digits (Decimal and Numeric data) columns, or the number of bits (Decimal and Numeric data) columns are allowed to be a part column of the primary key is the default value of the NULL column is the default value of the Identity column

Most of this information from the syscolumns table, except for only two. The default value actually from the Information_Schema.Columns view. Data type names (DataType Name) Available from the Systypes table and determines if a column is part of the primary key by a more complex syscolumns, sysindexes, and sysindexkeys tables. It is so complex that we encapsulate this feature into its own UDF. Let's take a look at the main features in Figure 3, it reveals more metadata information. This UDF is not too complicated. As you can see, most metadata information - except for some simple column renames - not do any modifications, including column name, column ID, length, precision, range, whether it is empty and data type first name. Next, you need to do a little additional work. For primary key metadata, we have created another UDF to determine if a column in the table is part of a table primary key. We will check these additional UDFS functions immediately. Let's take a look at the Alternate type and Identity status. The 8th bit (128) of the SYSCOLUMNS Status field indicates whether the column is a Identity column. (This is very important for what to create insert and Update scripts). Our simple formula implements this value to (& &) and package the result in the SIGN function. If the bit is set, it means that the column is the Identity column. C.Status & 128 will return the value 128. Otherwise, the value of 0 will be returned. The SIGN function returns to 1 when the positive value is returned to the -1,0 value when the negative number is returned. Therefore, if the column is evaluated as an Identity column, the return value 1 is returned, otherwise it returns 0. The Alternate type is used to indicate whether the data type requires additional information (length, accuracy or range) when defined. We use the CHARACTER and BINARY data type as the Alternate type, DECIMALS, and NUMERICS as 2, and other data types as 0. This value is used to determine whether the length, accuracy and scope needs to be added to the parameter definition.

Find primary key columns

As you can see, the information of the search column is not very difficult. Find a field that is part of the primary key. There is a list of fields that can be obtained. However, you want to find these fields to be in combination in syscolumns, sysindexes, and sysindexkeys tables and compare the columns we requested (transferred to UDF in the @scolumnname parameter). Therefore, the task of finding the primary key is easier to complete in a separate user-defined function, as we can package this work into a single function call. Let us examine this function and see the truth that happens:

Create Function DBO.FNISCOLUMNPRIMARYKEY

(@Stablename Varchar (128), @scolumnname varchar (128))

Returns bit

Begin

Declare @ntableid Int,

@nindexid Int,

@i int

Set @ntableid = Object_ID (@stablename)

This function contains two parameters, table names, and column names. If the specified column is part of the primary key in the table, the function will return a Bit flag. We will then declare the variables to be used during storage, and give the initial value. Now come to a interesting part: Find primary key information. We started to find an index ID for the primary key index of the table, 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

The index ID of the primary key index of this table is now given 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 the @nindexid is set to null value. If you exit the function, @nindexid contains an null value and returns 0 value. In other words, if there is no primary key index, then the column is not part of the primary key. Now we check the required columns (@scolumnname) in the list of primary keys.

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

With the previous INDEXID, we get the name from the Joint SysColumns and SysIndexKeys. These tables are combined by column ID and object IDs. To set the search criteria via the WHERE clause, we can only select the index column of the required table (Sik.ID = @NTableID), and only select the primary key index (Sik.indid = @nindexid). If @scolumnname is returned in the list of returned columns, otherwise, return value 0, and identify matching. ?

The default value of the column?

When a record is inserted into the table, if no value is given to a list, the default value will be the value of the column. Since the newly generated table is inserted, there is a parameter that is used for all possible columns that may be inserted, and the variable must include a value, even an null value, the default value of the table will not be used. Essentially, we will rewrite the default values of the column by clearly providing values for each column (even null). In order to neutralize the stored procedure we create, we must provide the default value when you insert data. Beyed later in this article we will see how to use the default value during the automated stored procedure. But now, let us first examine how to get those defaults. UDF we will use simply reference the information_schema.columns view, which provides a default value of a column. It is easier to get the default value with this view than using the SysConstraints system table. The next UDF simplifies this process by packaging the default value of the lookup logic to a simple function call.

Create function dbo.fncolumndefault (@stablename varchar (128),

@scolumnname varchar (128))

Returns varchar (4000)

Begin

Declare @SDefaultValue varchar (4000)

Select @sdefaultValue = dbo.fncleandefaultValue (column_default)

From information_schema.columns

Where table_name = @stablename

And colorn_name = @scolumnname

Return @SDefaultValue

End

The default value of the column is stored in a pair of parentheses, but we don't need it. So if you see, we pass the column_default? Field to another function fncleandefaultValue, which peels the brackets and return to the actual default. For example, if a column called NQTY has a default value 1, the column_default value will of course include (1). If the default is "Enter Text? Here", we get ("Enter Text Here"). Here is the source code of this UDF:

Create Function DBO.fncleandefaultValue (@sdefaultvalue varchar (4000))

Returns varchar (4000)

Begin

Return Substring (@SDefaultValue, 2, Datanceth (@SDEFAULTVALUE) -2)

End

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

Dynamic execution T-SQL

Dynamic T-SQL execution is the essence of our stored procedure, which allows you to write a generic T-SQL script and generate T-SQL scripts in order. It is the T-SQL execute command that allows common T-SQL scripts to perform specialized outputs, and create runtime stored procedures that will be used by the application. Execute or Exec actually have two functions: it can perform an existing stored procedure and dynamically performing a SQL command saved in a string. It is the latter feature that we will create these stored procedures in the joint of the acquired metadata. A simplified view of the process will use a large varchar variable with a stored procedure code that needs to create the stored procedure, then dynamically performs the content of this varchar variable at a time, create a new stored procedure. Let's take a simple example of a dynamic T-SQL: Create Proc Prgetauthor

@au_id char (11)

Declare @sexec varchar (8000)

Set @sexec = '' SELECT * from authors where au_id = '' '' ' @au_id ' '' '' ''

EXEC (@sexec)

In this example, we pass the Author's ID and connect it to a SELECT statement from an Author table. ? We call this stored procedure below:

Exec Prgetauthor '' 123-45-6789 ''

The prgetauthor stored procedure will create a SQL statement:

SELECT * from authors where au_id = '' 123-45-6789 ''

This statement will execute and return the ID of 123-45-6789 in Exec. As you can see, the design stored procedure will use this feature at a high level. But we should notice that this is not recommended for dynamic T-SQL. At any time, the dynamic T-SQL code is available for the external world, so there is the possibility of SQL attacks. We only use dynamic T-SQ L to achieve management and task purposes, which will never expose this function during any stored procedures that are available from anyone outside of the system and managers.

Create a stored procedure?

The first step in the stored procedure is quite standard when creating these design. Define stored procedures, declare variables, and variables initialization. Quickly browse these codes before establishing other stored procedures. We create two special string variables, a saved tab character, and save the carriage return. These can be built with UDFS, but we decided not to do this so that it is left to readers as exercises. They are used to assist in formatting of code output. Let's take a look at the beginning of this process, as shown in Figure 4. Again, there is no T-SQL new discovery here. We first check if there is a primary key. This will prevent our code from creating a potentially dangerous runtime stored procedure. Then set some variables and default values. The stored procedure first establishes a DROP statement for a new process, avoiding the stored procedure already exists, then create some comments, create a practical stored procedure definition (see several lines of Figure 1). You can modify these code to create a runtime stored procedure that is not existing (if there is anything else). This new feature will be set by the third option parameter @bifexistsdonothing. We will leave the reader as a simple practice. • The next code snippet starts to create a dynamic T-SQL process. For the definition of the new stored procedure (see Figure 5), add a disabled stored procedure and define the new stored procedure. Note How to use the second parameter (optional) @bexecute to determine if we want to actually run the code. In our definition of our automated stored procedure, this parameter is optional, the default value is 0, which means 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, and FntableColumnInfo is a function that returns a Table value. This function is used to replace complex T-SQL. In the four automated stored procedures, this UDF is only referenced in the declaration of the cursor. After declaring the cursor, we will open it and get 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 create a loop, until all values (@@ fetch_status = 0). Now we are preparing to analyze the information and create a key segment for the new stored procedure. • In the next code example, we use a cursor to loop and use the column metadata information to create a code. You will notice that there are three variables modified: @skeyfields, @ssetclause, and @SwhereClause. The first one is used to create a parameter list (including the CREATE PRDC segment in Figure 1) for the stored procedure. The second set statement used to set the update command in Figure 1. The last variable is used to set the last WHERE clause of Figure 1. Now let's verify the first part of the code (see Figure 7). • Figure 7 includes code that creates a parameter list for a new stored procedure. The first IF statement checks if it is ready to add data to the variable. If you are ready, we join a comma and a carriage return / wrap. We must correct each parameter in the parameter list. If there is no check, we will end with one or more commas. This error can be prevented from adding a comma before the next column. ? Next, we join a series of characters and metadata information, including a Tab character, a name, a column name, a space, and column type name.

Then we see if other information of the data type is required, check if accuracy, range, length and other information are required. If necessary, we additionally join these values in parentheses (such as T-SQL syntax). Finally, if the column is not an Identity column, and the column allows null or a timestamp (not allowed to be updated because it is automatically updated), then we add "= null" in the parameter definition. For example, the column of the discounts table in the database pubs is this: 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 the code will not be allowed to generate a code. These stored procedures depends on the primary key to determine how the data is updated. If there is no primary key, this automated stored procedure should modify, use all columns in the WHERE statement of the new stored procedure, or look up a unique index to use the WHERE clause. In other words, if possible, all tables should have primary keys, which is the basic principles of database design. ? Next, look at the code for the set statement created for the update command of the new store process (see Figure 8). Note How do we handle columns that are not primary keys. Again, if you want to update all columns, including columns in the primary key, you can simply delete the IF statement. Note that this IF can be an option feature, which is set by another parameter. In the last part, if we need it, we add a comma for the variable. In this example, if there is no data (meaning that we have not added any columns), we set the set statement in the variable to close. Next, we join a Tab character and require the column name of the data update, and one equal sign (=). In the Order_Details table, it should be ended by the following code:

Set unitprice = @Unitprice,

Quantity = @quantity,

Discount = @discount

Next, we have established a WHERE statement created for the new storage process. You will notice that the code section has an ELSE statement. This is the exception state of the primary key check, indicating that this column is part of the primary key, just running this code (see Figure 9). ? Again, or start this variable by the WHERE clause, or add an AND clause, which is determined whether it is the first item of the WHERE word. Then we join a Tab character, column name, string "= @" and column name. The results of the Order_Details example are as follows:

Where orderid = @orderID

And productID = @ProductID

Before the consolidation of the While cycle, we need to get the next row from the cursor and place the metadata value in the variable again. Once the cycle ends, we turn off and recycle the cursor. Now we can output any information for any new runtime stored procedure (see Figure 10) Finally, the design time stored procedure outputs T-SQL that generates a new runtime stored procedure, first join a carriage return to the SET statement ( Pure belongs to the format output purpose). Next, we join the key field (stored procedure parameters) and keyword AS (stored procedure definition requirements). Next, Update and the name of the updated table are added. Finally, we join the SET statement variable and the WHERE statement variable to end the definition of the stored procedure. Note that @ sprockText variables include T-SQL for new runtime stored procedures, you can choose to execute. If executed, the runtime stored procedure will be added to the database. in conclusion

The above is just one of the four automated stored procedures we developed. Of course, each stored procedure will change as needed. For example, the stored procedure is only used by the design when creating a runtime DELETE stored procedure uses the primary key of each table. All user-defined functions and stored procedures can be downloaded by links in front of this article. These stored procedures can join many other features, and some are what we have mentioned, such as using the same object name, the object exists, the creation of the audit index, and the stored procedure change declaration (maintenance stored procedure and mixed XML More effective). You can also create additional settings in a table to help code generation. In other words, these stored procedures can be used as the starting point of other automatic code generation tasks. In other examples, these codes will help you save a lot of time and effort, and even help you explore the T-SQL technology of interest.

9cbs

New Post(0)