Speed up Development on Backend and Middle Tier with SQL Scripts
page 1 of 6
Published: 23 Feb 2004
Unedited - Community Contributed
Ever wish you had a tool/robot that would allow you to generate your stored procedures and middle tier functions/classes for you and save you lot of time in coding? Most developers do get tired of coding with the same approach over and over. This article demonstrates how you can build your own SQL Procedures to simplify your repetitive work.
by Ameet Phadnis
Average Rating: 
Views (Total / Last 10 Days): 28636/ 88


          Many projects do access data through Stored Procedures. There are 5 standard stored procedures for tables in the database. The standard procedures are for


  1. Get: Getting a list from the table.
  2. GetSingle: Getting single record from the table.
  3. Add: Adding record to the table.
  4. Update: Updating the edited record
  5. 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


View Entire Article

User Comments

Title: MetaData   
Name: Sanjay Modi
Date: 2007-04-10 2:25:25 PM
its realy helpful my best wishes to user

Sanjay Modi
Title: ing   
Name: Jorgen Pedersen
Date: 2005-09-05 12:10:14 PM
Hi. I like your article but I need to find the name of the columns constraint name for the DEFAULT value of the column. Can you help?
Yjanks in advance
Title: Thank you   
Name: Mab
Date: 2005-06-21 8:37:32 AM
Its really a very gud articles for the developers. Thanks for sharing such a nice article.


Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-06-25 12:09:05 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search