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-2005 FunctionX, Inc.