Using a Stored Procedure With Argument

 
 
 

Introduction

We use this exercise as an introduction to a stored procedure that takes an argument. It is usually easy to use a simple stored procedure that only selects records, like a simple SELECT query.

This is an example of using a stored procedure that takes an argument. In this exercise, we have received a request that the company is going to apply a general raise to all employees but the management hasn't decided yet what the raise will be. Therefore, we create a form in which the user can enter the raise and click a button to submit the request.

 

Practical Learning: Using a Stored Procedure With Argument

 
  1. We will continue with the SuperMarket database created in our introduction to procedures

    Open SQL Query Analyzer with a new blank window and create a new procedure by typing the following code:
     
    -- creating the store procedure
    IF EXISTS (SELECT name 
    	   FROM   sysobjects 
    	   WHERE  name = N'GiveGeneralRaise' 
    	   AND 	  type = 'P')
        DROP PROCEDURE GiveGeneralRaise
    GO
    
    CREATE PROCEDURE GiveGeneralRaise 
    	@NewRaise SmallMoney
    AS
    	UPDATE Employees
            SET Salary = Salary + @NewRaise
    GO

  2. Execute the statement (F5)
  3. Start Visual C++ .NET and create a new Windows Forms Application named SuperMarket2
  4. In Server Explorer, expand the server that holds the above database and the database itself followed by its Tables node
  5. Drag Employees to the form
  6. On the main menu, click Data -> Generate Dataset...
  7. Change the name of the dataset to dsEmployees and click OK
  8. Design the form as follows (only the mention controls are important, you can ignore the others):
     

    Control Name Caption/Text Other Properties
    Form     StartPosition: CenterScreen
    DataGrid     Anchor: Top, Bottom, Left, Right
    AutoFormat: Professional 3
    DataSource: dsEmployees1.Employees
    Label   Give a raise to all employeees for $: Anchor: Bottom, Left
    TextBox txtNewRaise 0.15 TextAlign: Right
    Anchor: Bottom, Left
    Button btnNewRaise Submit Anchor: Bottom, Left, Right
  9. Double-click an empty the form to access its Load event
  10. Return to the form and double-click the Submit button
  11. Implement the events 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: System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)
    		 {
    			 this->sqlDataAdapter1->Fill(this->dsEmployees1);
    		 }
    
    private: System::Void btnNewRaise_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"GiveGeneralRaise", this->sqlConnection1);
    			 SqlDataAdapter *sdaNew = new SqlDataAdapter(cmdNew);
    			 DataSet * dsEmployees  = new DataSet();
    
    			 // We will need a SQL parameter to carry the argument
    			 // Declare its variable
    			 SqlParameter *parNew   = new SqlParameter();
    			 // Specify the name of the argument
    			 parNew->ParameterName  = S"@NewRaise";
    			 // Specify the SQL data type of the argument
    			 parNew->SqlDbType      = SqlDbType::SmallMoney;
    			 // Specify the value passed as argument
    			 parNew->Value          = this->txtNewRaise->Text;
    
    			 // Once the argument is ready, add it to the list of arguments
    			 cmdNew->Parameters->Add(parNew);
    		// Let the SQL command know the type of command we are going to use
    			 // In this case, it is a stored procedure
    			 cmdNew->CommandType = CommandType::StoredProcedure;
    
    			 // Inform the user about the action that is going to occur
    			 // (You can also do this after the action has been carried
     MessageBox::Show(String::Concat(S"All employees are going to receive a new raise of $",
                                     this->txtNewRaise->Text));
    
    		 // Update the data set with the new information from the data adapter
    			 sdaNew->Fill(dsEmployees, S"Employees");
    			 // Update the information displayed in the datagrid
    			 this->sqlDataAdapter1->Fill(this->dsEmployees1, S"Employees");
    		 }
    };
    }
  12. Test the application
     

 

 

Home Copyright 2004 FunctionX, Inc.