Automate the Generation of Stored Procedures for Your Database
Level of Difficulty 1 2 3
SUMMARY
Design-time automation makes coding faster and ensures that all the procedures generated use the same naming conventions and structure. In an effort to improve their coding efficiency in a large SQL project, the authors wrote a set of design-time stored procedures that generate run-time stored procedures, and have used them in project after project ever since. Recently, the authors updated their procedures to make use of SQL Server 2000 features, including user-defined functions.
This article covers the creation and execution of these dynamic T-SQL scripts to automate the coding of common database stored procedures.
Find Software from here Click here
A Simple Example
SQL Server System Tables and Views
Parsing a Table's Columns
Finding Primary Key Columns
Column Default Values
Dynamically Execute T-SQL
Creating Stored Procedures
ConclusionSome time ago, we began development on a rather large n-tier client/server project. During the initial planning, we decided to require a set methodology for accessing the numerous tables in the database. Four base stored procedures would be used to perform selects, inserts, updates and record deletions on each table. Although the required set of stored procedures would be similar in design, each table's unique column structure would mandate that significant details of each individual procedure would vary, thus making the writing of all the required procedures very tedious indeed. It became quite clear that the stored procedure writing process had to be automated.
What started out as an experiment turned into a core set of design-time stored procedures that write the base set of run-time stored procedures for all database tables in any given project. By building and using these design-time procedures, we not only saved ourselves hours of tedium, but saved our client some money as well, and came away with some nifty code that we still use today.
These design-time procedures have been updated to take advantage of some of the features of SQL Server™ 2000, specifically, user-defined functions (UDFs). So now the code is more modular, and we have additional functions available to us for other tasks.
Another bonus that design-time automation offers is the guarantee that the hundreds of procedures generated would be consistently structured and have a standard naming convention. In our case, all run-time procedure names that are generated are formatted as such: prApp_TableName_Task, where Task can be either Select, Insert, Update, or Delete. The procedures for the Customers and Orders tables would look something like this:
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 convention adds a great deal of organization to our database, making any given procedure easy to locate and making each procedure name self-describing. Developers will be able to quickly find and create code. And to top it off, future team members on the project will find the code and procedures easy to follow. Of course, if you already have a different naming convention in place, simply change a few lines of code and your conventions can be used instead.
These four design-time procedures are not set in stone, but are meant to act as a template for use in other projects. They are installed in the project database, and if needed, they are modified to suit the needs of the specific application. For example, in several of our applications, we added code to maintain an audit trail record in a separate database every time a record was modified.
A Simple Example
Before we begin, let's take a look at a simple example using the Order_Details table (whose name has been changed to replace a space with an underscore character) from the Northwind database. (Although spaces and other characters are allowed in object names, we recommend that you use regular identifiers for object names to prevent issues when using these automated stored procedures. See "Using Identifiers" in SQL Server Books Online for more information.)
The first task is to run the design-time procedure in order to create the run-time procedure that updates data in the Order_Details table:
EXEC pr__SYS_MakeUpdateRecordProc 'Order_Details'
For more please visit this link http://msdn.microsoft.com/en-us/magazine/cc188749.aspx#S1
SUMMARY
Design-time automation makes coding faster and ensures that all the procedures generated use the same naming conventions and structure. In an effort to improve their coding efficiency in a large SQL project, the authors wrote a set of design-time stored procedures that generate run-time stored procedures, and have used them in project after project ever since. Recently, the authors updated their procedures to make use of SQL Server 2000 features, including user-defined functions.
This article covers the creation and execution of these dynamic T-SQL scripts to automate the coding of common database stored procedures.
Find Software from here Click here
A Simple Example
SQL Server System Tables and Views
Parsing a Table's Columns
Finding Primary Key Columns
Column Default Values
Dynamically Execute T-SQL
Creating Stored Procedures
Conclusion
What started out as an experiment turned into a core set of design-time stored procedures that write the base set of run-time stored procedures for all database tables in any given project. By building and using these design-time procedures, we not only saved ourselves hours of tedium, but saved our client some money as well, and came away with some nifty code that we still use today.
These design-time procedures have been updated to take advantage of some of the features of SQL Server™ 2000, specifically, user-defined functions (UDFs). So now the code is more modular, and we have additional functions available to us for other tasks.
Another bonus that design-time automation offers is the guarantee that the hundreds of procedures generated would be consistently structured and have a standard naming convention. In our case, all run-time procedure names that are generated are formatted as such: prApp_TableName_Task, where Task can be either Select, Insert, Update, or Delete. The procedures for the Customers and Orders tables would look something like this:
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 convention adds a great deal of organization to our database, making any given procedure easy to locate and making each procedure name self-describing. Developers will be able to quickly find and create code. And to top it off, future team members on the project will find the code and procedures easy to follow. Of course, if you already have a different naming convention in place, simply change a few lines of code and your conventions can be used instead.
These four design-time procedures are not set in stone, but are meant to act as a template for use in other projects. They are installed in the project database, and if needed, they are modified to suit the needs of the specific application. For example, in several of our applications, we added code to maintain an audit trail record in a separate database every time a record was modified.
A Simple Example
Before we begin, let's take a look at a simple example using the Order_Details table (whose name has been changed to replace a space with an underscore character) from the Northwind database. (Although spaces and other characters are allowed in object names, we recommend that you use regular identifiers for object names to prevent issues when using these automated stored procedures. See "Using Identifiers" in SQL Server Books Online for more information.)
The first task is to run the design-time procedure in order to create the run-time procedure that updates data in the Order_Details table:
EXEC pr__SYS_MakeUpdateRecordProc 'Order_Details'
For more please visit this link http://msdn.microsoft.com/en-us/magazine/cc188749.aspx#S1
Comments