Click here to Skip to main content
13,090,299 members (78,486 online)
Click here to Skip to main content
Add your own
alternative version


61 bookmarked
Posted 29 Dec 2009

SQL Stored Procedure Generator

, 29 Dec 2009
Rate this:
Please Sign up or sign in to vote.
A simple application to generate Stored Procedures for existing tables in a SQL Server database.


The beginning of any new project/application mostly starts with a database, some classes, and then the UI. One of the most boring/tedious elements to this is generating the Stored Procedures needed for simple CRUD operations, as well as the class that references the database with all its methods etc.


I searched pretty far and wide for a simple and decent application that would take an existing table from a database and generate the most common Stored Procedures for me, as well as script out the C# code to call these procs. There weren't too many useful applications that worked for me (or actually worked!).

Using the Code

By iterating through a selected table's columns, we can generate the script as need be. By treating some columns differently (i.e., making them appear in a Where clause, or knowing they can't be updated), the scripting can become fairly intelligent. (See example in code samples below.)

Here, a picture is worth quite a few words. The picture above shows the operations of the application.

Shown below is some code which repeats itself in various ways throughout the app. It's a mixture of SqlClient and SqlSMO operations to interrogate a database and its tables/columns etc.

//Code to iterate tables             
private ServerConnection serverConnection = null;

private void btnGo_Click(object sender, EventArgs e)
    this.Cursor = Cursors.WaitCursor;
    SqlConnection objCn = new SqlConnection(sqlControl1.ConnectionString);
    serverConnection = new ServerConnection(objCn);
    Server server = new Server(serverConnection);
    TableCollection objTables = 

    foreach (Table objTable in objTables)
    this.Cursor = Cursors.Default;

//Snippet to show column iteration and generation of script.
if (chkSelect.Checked)
    strSQL = "CREATE PROCEDURE [GetAll" + 
                  strTableName + "] " + Environment.NewLine;
    strSQL += " As Select ";

    foreach (Column item in server.Databases[
        strSQL += "[" + item.Name.ToString() + "]";
        if (keyCount < server.Databases[
           strSQL += "," + Environment.NewLine;

    strSQL +=" from [" + strTableName + "]";
    txtSQL.Text += strSQL + Environment.NewLine + Environment.NewLine;

Below is a screenshot of a sample table, and the subsequent generated scripts.

CREATE PROCEDURE [GetAllVariables] As Select [ID], 
  [VarName], [VarDesc], [VarType] from [Variables]
CREATE PROCEDURE [DeleteVariables] (@ID int) As Delete from [Variables] Where ID = @ID
CREATE PROCEDURE [AddVariables] (@VarName varchar, @VarDesc varchar, @VarType varchar) 
  As Insert Into [Variables] ( VarName, VarDesc, VarType) 
  Values ( @VarName, @VarDesc, @VarType) select SCOPE_IDENTITY()
CREATE PROCEDURE [UpdateVariables] (@ID int, @VarName varchar, 
  @VarDesc varchar, @VarType varchar) As Update [Variables] set 
  VarName = @VarName, VarDesc = @VarDesc, VarType = @VarType Where ID = @ID
CREATE PROCEDURE [AddUpdateVariables] (@ID int, @VarName varchar, @VarDesc varchar, 
  @VarType varchar) As Begin If (Select ID from [Variables] 
  Where ID = @ID) <> 0 Begin Update [Variables] set VarName = @VarName, 
  VarDesc = @VarDesc, VarType = @VarType Where ID = @ID End Else Begin Insert 
  Into [Variables] ( VarName, VarDesc, VarType) 
  Values ( @VarName, @VarDesc, @VarType) select SCOPE_IDENTITY() End End 

Points of Interest

Nothing too special here about this project, just some string manipulation and working with the SQL SMO objects.

Note: The "Save To File" button will save each script individually. Also, I include "select SCOPE_IDENTITY()" in my insert procedures, because that's something I use quite often.


This is the first iteration. The next one will be adding the ability to generate the classes and interfaces for a table.


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


About the Author

Founder Jayess Software
South Africa South Africa
Jayess Software is a provider of both outsourced development resources, and developed solutions

You may also be interested in...

Comments and Discussions

QuestionSP Gen using Pin
Member 435095014-Feb-14 2:52
memberMember 435095014-Feb-14 2:52 
SuggestionSmall suggestion Pin
Member 978640120-Oct-13 6:25
memberMember 978640120-Oct-13 6:25 
QuestionGreat Pin
Member 920344323-Nov-12 23:05
memberMember 920344323-Nov-12 23:05 
SuggestionFail to save procedure Pin
Member 900291119-Jul-12 6:02
memberMember 900291119-Jul-12 6:02 
GeneralMy vote of 5 Pin
ESST22-Oct-10 18:42
memberESST22-Oct-10 18:42 
GeneralNice article Pin
jkpieters4-Feb-10 1:26
memberjkpieters4-Feb-10 1:26 
GeneralMy vote of 2 Pin
Simon Hughes6-Jan-10 2:33
memberSimon Hughes6-Jan-10 2:33 
GeneralSmall suggession Pin
chvbreddy4-Jan-10 22:21
memberchvbreddy4-Jan-10 22:21 
RantRe: Small suggession Pin
Gary Noter5-Jan-10 11:34
memberGary Noter5-Jan-10 11:34 
GeneralOh NO!!! Pin
David Lean4-Jan-10 16:58
memberDavid Lean4-Jan-10 16:58 
GeneralRe: Oh NO!!! Pin
jkpieters4-Feb-10 1:22
memberjkpieters4-Feb-10 1:22 
Show us how you would do this...


QuestionRe: Oh NO!!! Pin
CrshTstDmmy11-Jan-11 7:50
memberCrshTstDmmy11-Jan-11 7:50 
Irasimus30-Dec-09 6:16
memberIrasimus30-Dec-09 6:16 
Mi.Chal.31-Dec-09 1:33
memberMi.Chal.31-Dec-09 1:33 
GeneralRe: SCOPE_IDENTITY??? Pin
Irasimus6-Jan-10 10:31
memberIrasimus6-Jan-10 10:31 
GeneralVery Good Pin
Syed M Hussain30-Dec-09 2:48
memberSyed M Hussain30-Dec-09 2:48 
GeneralGood article Pin
ColinBud29-Dec-09 23:29
memberColinBud29-Dec-09 23:29 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170813.1 | Last Updated 30 Dec 2009
Article Copyright 2009 by JayessSoftware
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid