FunctionX Practical Learning Logo

Introduction to Procedures
 

Introduction

Many languages use the word procedure for what is known in C/C++ as function. Therefore, a SQL stored procedure is a type of function created as a SQL statement. SQL Server has a formal concept of a function that is distinct from a stored procedure. In fact, unlike C++, a stored procedure in SQL is treated like an object. Although it is created with (relatively simple and short) code, it is stored in its own file and, when needed, it must be explicitly called.
 

Practical LearningPractical Learning: Creating the Database
  1. Start Microsoft SQL Server Enterprise Manager or the SQL Query Analyzer
  2. Create a new database named SuperMarket
  3. Create a new table named Employees as follows:
     
  4. Fill it up with a few records as follows:
     
  5. Close the table
  6. In SQL Query Analyzer, open a new blank window and create a new procedure by typing the following code:
      
    -- =============================================
-- Procedure: CreateEmailAddress
--            Creates an email address for each employee
-- =============================================
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'CreateEmailAddress' 
	   AND 	  type = 'P')
    DROP PROCEDURE CreateEmailAddress
GO

CREATE PROCEDURE CreateEmailAddress
AS
UPDATE  Employees
               SET EmailAddress = LOWER(LastName) + 
                                  LOWER(LEFT(FirstName, 1)) + 
		                 '@supermarket.com'
	FROM Employees
GO
  7. Execute the statement (F5)
  8. Start Visual C++ .NET and create a new Windows Forms Application named SuperMarket1
  9. In Server Explorer, expand the server that holds the above database and the database itself followed by its Tables node
  10. Drag Employees to the form
  11. On the main menu, click Data -> Generate Dataset...
  12. Change the name of the dataset to dsEmployees and click OK
     
  13. Add a DataGrid control to the form as follows:
     
    Sally Super Market - Employees Records
    Control Name Text Other Properties
    Form     StartPosition: CenterScreen
    DataGrid     Anchor: Top, Bottom, Left, Right
    AutoFormat: Professional 3
    DataSource: dsEmployees1.Employees
    Button btnEmailAddr Generate Email Addresses Anchor: Bottom, Right
    Button btnClose Close Anchor: Bottom, Right
  14. Double-click an empty the form to access its Load event
  15. Return to the form and double-click the Close button
  16. Implement the events as follows:
      
    private: System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)
	 {
		this->sqlDataAdapter1->Fill(this->dsEmployees1);
	 }

private: System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
	 {
		 Close();
	 }
  17. Test the application then close the form to return to Visual C++ .NET
  18. Double-click the Generate Email Addresses button and implement its event as follows:
      
    #pragma once


namespace SuperMarket1
{
	using namespace System;
	using namespace System::ComponentModel;
	using namespace System::Collections;
	using namespace System::Windows::Forms;
	using namespace System::Data;
	using namespace System::Drawing;
	using namespace System::Data::SqlClient;

	/// <summary> 
	/// Summary for Form1
	///
	/// WARNING: If you change the name of this class, you will need to change the 
	///          'Resource File Name' property for the managed resource compiler tool 
	///          associated with all .resx files this class depends on.  Otherwise,
	///          the designers will not be able to interact properly with localized
	///          resources associated with this form.
	/// </summary>
	public __gc class Form1 : public System::Windows::Forms::Form
	{	
	public:
		Form1(void)
		{
			InitializeComponent();
		}
 

	private:
		/// <summary>
		/// Required designer variable.
		/// </summary>
		System::ComponentModel::Container * components;

		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		void InitializeComponent(void)
		{
			
			. . .

		}	

private: System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)
	 {
		 this->sqlDataAdapter1->Fill(this->dsEmployees1);
	 }

private: System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
	 {
		 Close();
	 }

private: System::Void btnEmailAddr_Click(System::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(S"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, S"Employees");
		 this->sqlDataAdapter1->Fill(this->dsEmployees1, S"Employees");

		 // Let the user know that the assignment has been carried
 MessageBox::Show(S"An email address has been created for each employees");
	}
};
}
  19. Test the application:
     
 
Home Copyright © 2004-2012, FunctionX