Microsoft Visual C# Databases: Creating a Stored Procedure

	Creating a Stored Procedure

Introduction

A procedure can be as simple as calculating 124 + 68 or as complex as finding out if a date range includes a holiday for somebody who is renting a car so the day can be calculated with a different rate as compared to other dates in the same range. As always, we should start with simple examples. To create a procedure, you can use either Enterprise Manager or SQL Query Analyzer.

To create a new procedure in Enterprise Manager, after expanding the database, you can right-click it, position the mouse on New, and click Stored Procedure... You would be presented with a skeleton syntax that you can complete using the techniques we will learn in this lesson.

To create a new procedure in SQL Query Analyzer, after selecting the database (either from the combo box on the toolbar or with the USE keyword), you can type code based on the syntaxes we will learn shortly.

The creation of a procedure starts with the CREATE PROCEDURE expression. You can also use CREATE PROC. Both expressions produce the same result.

Like everything in your database, you must name your procedure. The name of a procedure can be any string that follows the rules we reviewed for naming objects. There are some other rules or suggestions you should or must follow when naming your procedure. For example, refrain from starting the name of a procedure with sp_ because it would conflict with some of the procedures that already ship with SQL Server.

After the name of the procedure, type the keyword AS.

The section, group of words, or group of lines after the AS keyword is called the body of the procedure. It states what you want the procedure to do or what you want it to produce.

Based on this, the simplest syntax of defining a procedure is:

CREATE PROCEDURE ProcedureName
AS
Body of the Procedure

It is important to keep in mind that there are many other issues related to creating a procedure but for now, let's consider that syntax.

The Simplest Procedures

Probably the simplest procedure you can write would consist of selecting columns from a table. This is done with the SELECT keyword. For example, to create a procedure whose job would consist of creating a list of car makes from a table named Cars, you would write:

CREATE PROCEDURE ListOfMakes
AS
SELECT Make

To execute this procedure, you would type:

EXECUTE ListOfMakes

You can also create a procedure that selects more than one column from a table. As done with the SELECT keyword in data analysis, you would separate each item of the list with a comma, except for the last. Here is an example:

CREATE PROCEDURE ListOfCars
AS
SELECT Make, Model, CarYear

Returning a Value

One of the advantages of using procedures is that not only can they produce the same expressions as we saw during analysis but also they can store such expressions to be recalled any time without having to re-write them. Based on this, you can create an expression that combines a first and a last name to produce and store a full name. Here is an example:

CREATE PROC GetFullName
AS
SELECT FullName = LastName + ', ' + LastName
FROM Students

Practical Learning: Creating a Stored Procedure

In Server Explorer, under the SuperMarket node, right-click Stored Procedures and click New Stored Procedure
Complete it as follows:
To save the procedure, on the Standard toolbar, click the Save button
Close the procedure window

Executing a Procedure

After creating a procedure, to get its result, you would need to execute it (in other programming languages, we would say that, in order to use a function, you must call it). To execute a procedure, you use the EXECUTE keyword followed by the name of the procedure. Although there are some other issues related to executing a procedure, for now, we will consider that the simplest syntax to call a procedure is:

EXECUTE ProcedureName

Alternatively, instead of EXECUTE, you can use the EXEC keyword:

EXEC ProcedureName

After a procedure has been executed, it is saved using its name. Since it becomes stored as an integral part of the database, a SQL procedure is also called a Stored Procedure.

Practical Learning: Using a Stored Procedure

On the form, add a button
Change its properties as follows:
Text: Generate Email Address
Anchor: Bottom, Right
Name to btnEmailAddress

Double-click the Generate Email Addresses button and implement its event as follows:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace SuperMarket1
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{	
		. . . No Change	
		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

		private void btnLoad_Click(object sender, System.EventArgs e)
		{
			this.sqlDataAdapter1.Fill(this.dsEmployees1);
		}

		private void btnClose_Click(object sender, System.EventArgs e)
		{
			Close();
		}

		private void btnEmailAddress_Click(object sender, System.EventArgs e)
		{
			// Create a new SQL command command, passing it the name of
			// the stored procedure we want to call and attaching it to 
			// the existing SQL connection
			SqlCommand cmdNew     = new SqlCommand("CreateEmailAddress", this.sqlConnection1);
		 
			// Create a new adapter and initialize it with the new SQL command
			SqlDataAdapter sdaNew = new SqlDataAdapter(cmdNew);
             			// Create a new data set
			DataSet dsEmployees  = new DataSet();

			// We need to specify the type of command we want to use.
			// In this case, it will be a SQL Server stored procedure
			cmdNew.CommandType = CommandType.StoredProcedure;

			// It is time to update the data set with information from the data adapter
			sdaNew.Fill(dsEmployees, "Employees");
			this.sqlDataAdapter1.Fill(this.dsEmployees1, "Employees");

			// Let the user know that the assignment has been carried
			MessageBox.Show("An email address has been created for each employees");
		}
	}
}

Test the application:
Close the form and return to your programming environment

Deleting a Procedure

One of the biggest characteristics of a stored procedure, as compared to functions in traditional languages, is that a procedure is treated like an object in its own right. Therefore, after creating it, if you don't need it anymore, you can get rid of it.

There are various types of procedures, some of which are considered temporary. Those types of procedures delete themselves when not needed anymore, such as when the person who created the procedure disconnects from the database or shuts down the computer. Otherwise, to delete a procedure, you can use either Enterprise Manager or SQL Query Analyzer. As mentioned with tables, even if you create a procedure in Enterprise Manager, you can delete it using SQL Query Analyzer and vice-versa.

To remove a procedure in Enterprise Manager, after expanding the database, click the Stored Procedure node. If you see the needed procedure in the list, fine. If you don't see the procedure in the list but know for sure that the procedure was created, for example if you create a new procedure in SQL Query Analyzer, it doesn't automatically appear in Enterprise Manager, make sure you refresh the list first by pressing F5. After locating the procedure in the right frame, you can either right-click it and click Delete, or click it to select it and then press Delete. Before the procedure gets removed, you would be warned with a dialog box to make your final decision.

To delete a procedure in SQL Query Analyzer using Transact-SQL, the syntax to use is:

DROP PROCEDURE ProcedureName

Of course, you should make sure you are in the right database and also that the ProcedureName exists.


Previous	Copyright © 2004-2006 FunctionX, Inc.	Next