Click here to Skip to main content
15,167,809 members
Articles / Database Development / SQL Server
Article
Posted 8 Aug 2005

Stats

36.4K views
759 downloads
44 bookmarked

Stored procedures generator

Rate me:
Please Sign up or sign in to vote.
3.11/5 (12 votes)
8 Aug 20052 min read
Generator for SQL Server stored procedures.

Introduction

The project presented serves for generating stored procedures for Insert, Update and Delete in a specified table.

Description

The job we most often have to do during writing queries is writing the basic stored procedures for manipulating data on the existing tables in a database. It is perhaps the step causing the most number of syntax errors. The application in this article helps avoid such errors. You just have to fill the required fields and your stored procedures are ready. Of course, the generated queries with some modifications can be used further in your code or server.

How to use

  1. Download the project to your computer.
  2. Compile/Run it.
  3. Enter the table name and number of columns.
  4. Press the button Reload.
  5. Fill the fields for primary keys, column names, data types, sizes and Allow Nulls.
  6. Before pressing the button Generate Queries, check: if there is at least one column with primary key and all columns aren't primary keys. This is important for the Update and Delete query to be generated.
  7. Press the Generate Queries button.

Example

Image 1

Fig.1. The window appears after running the project.

After filling the textboxes for table name and number of columns, click on Reload button:

Image 2

Fig.2. Fields for table columns are generated.

Next, after filling the column table data and clicking on Generate Queries button:

Image 3

Fig.3. Queries are created.

You can now copy them and use in your application simply by clicking on the Copy to Clipboard button, below each textbox.

Commenting the code

Let's see first the code executed on clicking the Reload button.

C#
try
{
  for(int i = 0; i<no; i++) colList[i].Dispose();
    no = Convert.ToInt32(txtColumnNo.Text);
  colList = new ucColumn[no];
  for(int i = 0; i<no; i++)
  {
    colList[i] = new ucColumn();
    colList[i].Location = new System.Drawing.Point(8, 90+i*28);
    colList[i].Size = new System.Drawing.Size(432, 21);
    this.Controls.Add(colList[i]);
  }
}
catch(Exception ex)
{
  MessageBox.Show("Please type valid integer for number of columns.", 
                  "Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}

At the beginning, the number typed for No. of columns is converted to an integer, which is used for declaring and creating the array of controls dynamically. The value for control distance step is carefully selected - 90+i*28 -corresponding to its dimension.

The code to for query generation follows, e.g. for Insert query:

C#
if(checkvalidity()=="")
{
  if((no > 0) && (txtTableName.Text != ""))
  {

Above code checks if the sizes for column types, number of columns and table name are given properly.

C#
txtInsertQuery.Text = 
    "CREATE PROCEDURE dbo.sp_Insert_" + txtTableName.Text;
txtInsertQuery.Text += "\r\n(";
if(no>1)
{
  for(int i=0;i<no-1;i++)
    txtInsertQuery.Text += "\r\n @"+colList[i].txtColumn.Text + 
         " "+colList[i].cmbType.Text+
         createtype(colList[i].txtSize.Text)+ 
         allownull(colList[i].cmbNull.Text)+",";
    txtInsertQuery.Text += "\r\n @"+colList[no-1].txtColumn.Text + 
         " "+colList[no-1].cmbType.Text + 
         createtype(colList[no-1].txtSize.Text)+
         allownull(colList[no-1].cmbNull.Text);
}
else
{
  txtInsertQuery.Text += "\r\n @"+colList[0].txtColumn.Text+ 
         " "+colList[0].cmbType.Text+
         createtype(colList[0].txtSize.Text)+
         allownull(colList[0].cmbNull.Text);
}
txtInsertQuery.Text += "\r\n)";

Then comes creation of the header of the procedure and the declaration of variables.

C#
txtInsertQuery.Text += 
  "\r\nAS\r\nINSERT INTO "+txtTableName.Text+"(";
if(no>1)
{
  for(int i=0;i<no-1;i++)
    txtInsertQuery.Text += colList[i].txtColumn.Text+", ";
  txtInsertQuery.Text += colList[no-1].txtColumn.Text+")";
}
else
{
  txtInsertQuery.Text += colList[0].txtColumn.Text+")";
}
txtInsertQuery.Text += "\r\nVALUES(";

if(no>1)
{
  for(int i=0;i<no-1;i++)
    txtInsertQuery.Text += "@"+colList[i].txtColumn.Text+", ";
  txtInsertQuery.Text += "@"+colList[no-1].txtColumn.Text+")";
}
else
{
  txtInsertQuery.Text += "@"+colList[0].txtColumn.Text+")";
}
txtInsertQuery.Text += "\r\nGO";

And the query for inserting is thus created.

The code for Update and Delete is similar with some additional requirements.

Happy querying!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Kujtim Hyseni
Web Developer
Albania Albania
Kujtim Hyseni

Comments and Discussions

 
GeneralNice Shot..... Pin
Domingo M. Asuncion27-Mar-07 21:27
MemberDomingo M. Asuncion27-Mar-07 21:27 
GeneralA complete solution Pin
chris Liang30-Aug-05 10:15
Memberchris Liang30-Aug-05 10:15 
GeneralRe: A complete solution - a free one! Pin
Thomas Schittli28-Sep-05 4:11
MemberThomas Schittli28-Sep-05 4:11 
... or just use one of the many free tools like MyGenerationBig Grin | :-D :
www.mygenerationsoftware.com

Kind regards,
thomas

People who wait until the eleventh hour to call on Jesus die at 10:30.

-- modified at 9:43 Wednesday 28th September, 2005
GeneralRe: A complete solution - a free one! Pin
Stixoffire29-Jun-06 10:37
MemberStixoffire29-Jun-06 10:37 
Generalvery good Pin
moonxp8-Aug-05 17:29
Membermoonxp8-Aug-05 17:29 
GeneralRecommendation Pin
James Taylor8-Aug-05 8:54
MemberJames Taylor8-Aug-05 8:54 
GeneralIn the next version Pin
Kujtim Hyseni8-Aug-05 21:19
MemberKujtim Hyseni8-Aug-05 21:19 
GeneralRe: In the next version Pin
Stixoffire29-Jun-06 10:39
MemberStixoffire29-Jun-06 10:39 

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.