Click here to Skip to main content
11,929,232 members (62,321 online)
Click here to Skip to main content
Add your own
alternative version


38 bookmarked

Stored Procedure Generator

, 20 Jun 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
This utility creates basic Select, Insert, Update and Delete Stored procedure for the selected Tables.


If we use dataset as a data access object every time we create a new project, after the database design is complete we wish to create the basic stored procedures for each and every table such as Select, Insert, Update and Delete. If a table has 10 columns, it's a real pain to write annoying stored procedures which do not need brains. I decided to create a utility which can create these stored procedures for us, in a click (for all the tables in the selected database).


My motive for this application was to automate stored procedures, so for browsing server and database, I nicked in code from Michael Potter. You can find his article from this link.

Using the Code

To start with, this utility allows you to browse through available servers:

Screenshot - SelectServer.jpg

After the database is selected, you want to select a valid database, if you know what you want, you can just type in these two. For database, the precondition is: you must provide a valid username and password which is created in that particular server and it should have access to the database you are going to select.

Screenshot - SelectDatabase.jpg

After selecting the database, you can click on the button "Get Tables". This fires an event (Stored Procedure) to get all the table names in the database selected.

  @TableCatalog AS VARCHAR(100)
) AS
and       TABLE_CATALOG = @TableCatalog
and       TABLE_NAME <>   'dtproperties'

This comes up with tables as shown below:

Screenshot - Create_Stored_Procedure.jpg

You have a choice to select the stored procedure you want to create: Select, Insert, Update, and Delete. Once you have selected, click on "Create procedure" button, the system will get all the columns for each table through the following stored procedure.

@TableName AS VARCHAR(100) 
) AS 
WHERE TABLE_Name = @TableName 

P.S.: The system searches for the above stored procedure and if it does not find one, it creates it, so no worries.

The system now has all the data it needs. Now we can create the stored procedure as needed. For example, to create "Select Stored procedure", we can do something like this:

  selectBuilder.AppendLine(string.Format("[{0}Select] ( @{1} AS INT) AS ", 
                           tableName, columnTable.Rows[0]["Column_Name"])); 
  selectBuilder.AppendLine("SELECT ");
  foreach (DataRow row in columnTable.Rows) 
    selectBuilder.Append(string.Format("[{0}],", row["Column_Name"])); 
  selectBuilder = RemoveLastComma(selectBuilder); 
  selectBuilder.AppendLine(string.Format(" FROM [{0}] ", tableName)); 
  selectBuilder.Append(string.Format("WHERE [{0}] = @{0}", 
  DataAccess.CreateProcedure(selectBuilder.ToString(), connection); 
catch (Exception ex) 
  throw new Exception(ex.Message); 

Points of Interest

While creating this utility, I just had a thought that an Enterprise Object Framework can be created, which can be an extension to this. Well, I will continue doing that.

P.S.: The code creates and audits the stored procedures created.


  • 20th June, 2007: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
Australia Australia
I am working as a Microsoft .Net Analyst\Programmer in Transact (Telecommunication commpany), located in Australian Capital territory. Its been 3 years since I am doing .Net development work. Finally I have decided to jump into "code project" to contribute my learnings over the years.
I am thinking of completing MCPD so wish me luck !! Smile | :)

You may also be interested in...

Comments and Discussions

QuestionThanks Pin
JasRaj Bishnoi14-Nov-13 20:16
memberJasRaj Bishnoi14-Nov-13 20:16 
GeneralError Pin
Yasin7528-Jun-07 20:50
memberYasin7528-Jun-07 20:50 
GeneralRe: Error Pin
RepliCrux28-Jun-07 20:52
memberRepliCrux28-Jun-07 20:52 
GeneralImprovements Pin
mokles28-Jun-07 3:36
membermokles28-Jun-07 3:36 

Nice Try. I would prefer to have a Stored Procedure create these kind of stored procedures. Because, most of the time, you need to change the created script to suit your need. So, it is better to be in the same enviroment.
Anyway, the link has code that will let you use Windows Authentication (That utility is quite useful, although not for the same purpose), and give your database names in a drop down list. Much better that way.

Questionuse existing tools? Pin
i61825-Jun-07 22:00
memberi61825-Jun-07 22:00 
AnswerRe: use existing tools? Pin
RepliCrux25-Jun-07 22:05
memberRepliCrux25-Jun-07 22:05 
GeneralRe: use existing tools? Pin
Jasmine250126-Jun-07 9:14
memberJasmine250126-Jun-07 9:14 
GeneralWindows Authentication Pin
spoodygoon21-Jun-07 1:24
memberspoodygoon21-Jun-07 1:24 
GeneralRe: Windows Authentication Pin
RepliCrux21-Jun-07 1:30
memberRepliCrux21-Jun-07 1:30 
GeneralComments and suggestion Pin
Nickolay Karnaukhov20-Jun-07 23:56
memberNickolay Karnaukhov20-Jun-07 23:56 
GeneralRe: Comments and suggestion Pin
Nickolay Karnaukhov20-Jun-07 23:59
memberNickolay Karnaukhov20-Jun-07 23:59 
GeneralRe: Comments and suggestion Pin
RepliCrux21-Jun-07 1:18
memberRepliCrux21-Jun-07 1:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.151126.1 | Last Updated 21 Jun 2007
Article Copyright 2007 by RepliCrux
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid