Many projects do access data through Stored Procedures. There are 5 standard stored procedures for tables in the database. The standard procedures are for
-
Get: Getting a list from the table.
-
GetSingle: Getting single record from the table.
-
Add: Adding record to the table.
-
Update: Updating the edited record
-
Delete: Deleting single record.
Before I wrote my first Stored Procedure generator, I used to create all these procedures manually. So, in case of 10 tables database I had to write 50 stored procedures manually. This used to take time and I was getting tired doing the same job over and over again. Also, in case of complicated/large tables it used to leave room for ID10T (IDIOT) errors or typing errors. So, I started thinking of designing a SQL procedure which will generate all these procedures for me. The first Stored Procedure Generator I wrote was using System tables. But System tables can change in future versions of SQL Server, that’s when I found out that SQL Server provides Information Schema views on most of the information on the data. So, I started writing another Stored Procedure generator which is well organized and uses information schema views. In this article, I will explain different ways of accessing meta data and also I will give an example of Insert procedure generator.
This article will initially explain the System Tables and System Stored Procedures, then I will explain the recommended Information Schema Views. I have structured it in this fashion to explain the complexity of System Tables and how easy it is with Information Schema Views. Readers can jump directly to Information Schema Views section.
The same principle of building Stored Procedures do apply for Business functions or building Data Access Layer functions that do access Stored Procedures.
This article will provide you some examples on building Stored Procedure Generator and also building VB function Generator.
Free Download of complete Stored Procedure Generator and DNN VB Function Generator is available at www.etekglobalInc.com
