Home

Procedures Arguments and Parameters
 

Introduction

Imagine you are creating an application for a department store that sometimes applies discounts of 10%, 20%, 40%, 55%, 70%, etc on items its sells. Since the management decides when and what discount would be applied on an item, you cannot predict all possibilities. One way to solve this type of problem is to create a procedure that would receive the discount applied on an item and then apply this discount to the price of the item.

All of the procedures we have created and used so far assumed that the values they needed were already in a table of the database. In some cases, you may need to create a procedure that involves values that are not part of the database. On such a scenario, for the procedure to carry its assignment, you would supply it with one or more values. 

An external value that is provided to a stored procedure is called a parameter. When you create a procedure, you must also create the parameter if you judge it necessary. When a procedure's creation is equipped with a parameter, it is said that the procedure takes an argument. A procedure can also take more than one argument.

When you execute a procedure that takes one or more arguments, you must provide a value for each argument. In this case, you are said to pass a value for the argument. There are cases when you don't have to provide an argument. We will learn how this is done.

Passing Arguments

To create a procedure that takes an argument, type the formula CREATE PROCEDURE or CREATE PROC followed by the name of the procedure, then type the name of the argument that starts with @. The parameter is created like a column of a table. That is, a parameter must have a name, a data type and an optional length. Here is the syntax you would use:

CREATE PROCEDURE ProcedureName
@ParameterName DataType
AS
Body of the Procedure

When implementing the procedure, you can define what you want to do with the parameters, in the body of the procedure. One way you can use a parameter is to run a query whose factor the user would provide. For example, imagine you want to create a procedure that, whenever executed, it would be supplied with a gender, then it would display the list of students of that gender. Since you want the user to specify the gender of students to display, you can create a procedure that receives the gender. Here is an example:

CREATE PROC GetListOfStudentsByGender
@Gdr VARCHAR(12)
AS
SELECT FirstName, LastName, DateOfBirth, HomeOfBirth, HomePhone, Gender
FROM Students
WHERE Gender = @Gdr

As mentioned already, when executing a procedure that takes a parameter, make sure you provide a value for the parameter. The syntax used is:

EXEC ProcedureName ParameterValue

If the parameter is Boolean or numeric, make sure you provide an appropriate value. If the parameter is a character or a string, type its value in single-quotes. Here is an example:

Notice that we could/should have omitted to include the Gender column in the statement since it would be implied to the user.

Another type of procedure can be made to take more than one parameter. In this case, create the parameter in the section before the AS keyword, separated by a comma. The syntax you would use is:

CREATE PROCEDURE ProcedureName
@ParameterName1 DataType, @ParameterName2 DataType, @ParameterName_n DataType
AS
Body of the Procedure

When calling a procedure that takes more than one parameter, you must still provide a value for each parameter but you have two alternatives. The simplest technique consists of providing a value for each parameter in the exact order they appear in the procedure.

Alternatively, you can provide the value for each parameter in the order of your choice. In this case, you must type the name of each parameter and assign it the corresponding value.

  
 

Practical Learning: Passing Arguments to a  Stored Procedure
  1. We will continue with the SuperMarket database
    In Server Explorer, under the SuperMarket  node, right-click Stored Procedures and click New Stored Procedure
  2. Complete it as follows:
     
  3. Save and close the procedure window
  4. Display the form (Form1.cs [Design])
  5. Change the design of the form as follows:
     
     
    New Control Name Text Other Properties
    Label   Give a raise of $ Anchor: Bottom, Left
    TextBox txtNewRaise 0.15 Anchor: Bottom, Left
    AlignText: Right
    Label   to all employees Anchor: Bottom, Left
    Button btnSubmit Submit Anchor: Bottom, Left
  6. Double-click the Submit button and implement its Click event as follows:
      
    private void btnSubmit_Click(object sender, System.EventArgs e)
{
	// Create a new SQL 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("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  = "@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("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, "Employees");
	// Update the information displayed in the datagrid
	this.sqlDataAdapter1.Fill(this.dsEmployees1, "Employees");
}
  7. Execute the application
     
  8. Close the form and return to your programming environment

Default Arguments

Imagine you create a table for a department store and the table would be used to hold the names and prices of items (in this example, the table is called SaleItems):

Table Properties

Suppose you have filled the table with a few items as follows:

Sale Items

Imagine you want to create a mechanism of calculating the price of an item after a discount has been applied to it. Such a procedure can be created as follows:

CREATE PROC CalculateNetPrice
@discount Decimal
AS
SELECT ItemName, ItemPrice - (ItemPrice * @discount / 100)
FROM SaleItems

This can be executed as follows:

Procedure Executed

If you are planning to create a procedure that takes an argument and know that the argument will likely have the same value most of the time, you can provide that value as parameter but leave a room for other values of that argument. A value given to an argument is referred to as default. What this implies is that, when the user calls that stored procedure, if the user doesn't provide a value for the argument, the default value would be used.

To create a procedure that takes an argument that carries a default value, after declaring the value, on its right side, type = followed by the desired value. Here is an example applied to the above database:

CREATE PROC CalculateNetPrice
@discount Decimal = 20.00
AS
SELECT ItemName, ItemPrice - (ItemPrice * @discount / 100)
FROM SaleItems

When executing a procedure that takes a default argument, you don't have to provide a value for the argument if the default value suits you. Based on this, the above procedure can be called as follows:

If the default value doesn't apply to your current calculation, you can provide a value for the argument. Here is an example:

Using this same approach, you can create a procedure that takes more than one argument with default values. To provide a default value for each argument, after declaring it, type the desired value to its right side. Here is an example of a procedure that takes two arguments, each with a default value:

 
CREATE PROC CalculateSalePrice2
@Discount Decimal = 20.00,
@TaxRate  Decimal = 7.75
AS
SELECT ItemName As [Item Description],
       ItemPrice As [Marked Price],
       ItemPrice * @Discount / 100 As [Discount Amt],
       ItemPrice - (ItemPrice * @Discount / 100) As [After Discount],
       ItemPrice * @TaxRate / 100 As [Tax Amount],
       (ItemPrice * @TaxRate / 100) + ItemPrice - (ItemPrice * @Discount / 100) + (@TaxRate / 100) As [Net Price]
FROM SaleItems

Here is an example of executing the procedure:

When calling a procedure that takes more than one argument and all arguments having default values, you don't need to provide a value for each argument, you can provide a value for only one or some of the arguments. The above procedure can be called with one argument as follows:

EXEC CalculateSalePrice2 55.00

In this case, the other argument(s) would use their default value.

We saw that, when calling a procedure that takes more than one argument, you didn't have to provide the values of the argument in the exact order they appear in the procedure, you just had to type the name of each argument and assign it the corresponding value. In the same way, if a procedure takes more than one argument and some of the arguments have default values, when calling it, you can provide the values in the order of your choice, by typing the name of each argument and assigning it the desired value. Based on this, the above procedure can be called with only the value of the second argument as follows:

EXEC CalculateSalePrice2 @TaxRate = 8.55

In this case, the first argument would use its default value.
 

Output Parameter

Many languages use the notion of passing an argument by reference. This type of argument is passed to a procedure but it is meant to return a value. Transact-SQL uses the same technique. In other words, you can create a procedure that takes a parameter but the purpose of the parameter is to carry a new value when the procedure ends so you can use that value as you see fit.

To create a parameter that will return a value from the procedure, type the OUTPUT keyword on the right side of the parameter. A syntax you can use is:

CREATE PROCEDURE ProcedureName
@ParameterName DataType OUTPUT
AS
Body of the Procedure

You can also create a procedure that takes a mix of value and output parameters.
 
 
Previous Copyright © 2004-2006 FunctionX, Inc. Next