Home

Functions Arguments

   

Introduction

 

In order to carry its assignment, a function can be provided with some values. Put it another way, when you create a function, instead of, or in addition to, local variables, you may want the code that will call the function to provide the values needed to perform the assignment. For example, imagine you want to create a function that would generate employees email addresses when a user has entered a first and last name. At the time you are creating the function, you cannot know or predict the names of employees, including those who have not even been hired yet. In this case, you can write the whole function but provide one or more placeholders for values that would be supplied when the function is called.

An external value that is provided to a function is called a parameter. A function can also take more than one parameter. Therefore, when you create a function, you also decide whether your function would take one or more parameters and what those parameters, if any, would be.

A Parameterized Function

We have already seen that a function's name is also followed by parentheses. If the function doesn't use an external value, its parentheses can be left empty. If a function will use an external value, when you create the function, you must specify a name and the type of value of the parameters. The name of the parameter is created with the @ sign, like a variable as we saw in the previous lesson. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2))

When a function takes a parameter, in the body of the function, you can use the parameter as if you knew its value, as long as you respect the type of that value. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
    RETURN @Number1 + 1450
END

Calling a Parameterized Function

When you call a function that takes one parameter, you must supply a value for that argument. To do this, type the value of the parameter in the parentheses of the function. Here is an example:

A Function With Various Arguments

Instead of only one parameter, you can also create a function that takes more than one parameter. In this case, separate the arguments in the parentheses of the function with a comma. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2), @Number2 Decimal(6,2))

Once again, in the body of the function, you can use the parameters as if you already knew their value. You can also declare local variables and involve them with parameters as you see fit. Here is an example:

CREATE FUNCTION Addition(@Number1 Decimal(6,2),
			 @Number2 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
    DECLARE @Result Decimal(6,2)
    SET @Result = @Number1 + @Number2
    RETURN @Result
END;
GO

When calling a function that takes more than one parameter, in the parentheses of the function, provide a value for each parameter, in the exact order they appear in the parentheses of the function. Here is an example:

PRINT Variables1.dbo.Addition(1450, 228);

You can also pass the names of already declared and initialized variables. Here is an example that calls the above function:

DECLARE @Nbr1 Decimal(6,2),
        @Nbr2 Decimal(6,2)
SET @Nbr1 = 4268.55
SET @Nbr2 =26.83
SELECT @Nbr1 As First,
       @Nbr2 As Second,
       Variables1.dbo.Addition(@Nbr1, @Nbr2) AS Result

This would produce:

Practical LearningPractical Learning: Creating and Calling a Function with Argument

  1. On the form, double-click the Database button and change its code as follows:
     
    private void btnDatabase_Click(object sender, EventArgs e)
    {
        string strConnection =
    		"Data Source=(local);" +
    		"Database='UtilityCompany1';" +
    		"Integrated Security=yes;";
        // The following function is used to calculate the customer's next bill
        // Some of the empty spaces, not required,
        // are meant to make the code easier to read
        string CreateFunction = "CREATE FUNCTION EvaluateInvoice(@Counter int) " +
    			    "RETURNS decimal(6, 2) " +
    			    "AS " +
    			    "BEGIN " +
    			    "	 DECLARE @BaseCharge money, " +
    			    "	 	 @Counter0To50 int, " +
    			    "	 	 @Counter50To150 int, " +
    			    "	 	 @Counter150To200 int, " +
    			    "	 	 @CounterOver200 int, " +
    			    "	  	 @First50 decimal(6, 2), " +
    			    "	 	 @FiftyTo150 decimal(6, 2), " +
    			    "	 	 @OneFiftyTo200 decimal(6, 2) ," +
    			    "	 	 @Over200 decimal(6, 2), " +
    			    "	 	 @TotalCharge money; " +
    
    			    "	 SET @BaseCharge = 8.50; " +
    			    "	 SET @Counter0To50 = 0; " +
    			    "	 SET @Counter50To150 = 0; " +
    			    "	 SET @Counter150To200 = 0; " +
    			    "	 SET @CounterOver200 = 0; " +
    			    "	 SET @First50 = 0.00; " +
    			    "	 SET @FiftyTo150 = 0.00; " +
    			    "	 SET @OneFiftyTo200 = 0.00; " +
    			    "	 SET @Over200 = 0.00; " +
    			    "	 SET @TotalCharge = 0.00; " +
    
    			    "	 IF @Counter <= 50 " +
    			    "	     BEGIN" +
    			    "	 	SET @Counter0To50 = @Counter; " +
    			    "	 	SET @Counter50To150 = 0; " +
    			    "	 	SET @Counter150To200 = 0; " +
    			    "	 	SET @CounterOver200 = 0; " +
    			    "	     END;" +
    
    			    "	 IF (@Counter > 50) AND (@Counter <= 150) " +
    			    "	     BEGIN" +
    			    "	 	SET @Counter0To50 = 50; " +
    			    "	 	SET @Counter50To150 = @Counter - 50; " +
    			    " 		SET @Counter150To200 = 0; " +
    			    " 		SET @CounterOver200 = 0; " +
    			    " 	     END;" +
    
    			    "	 IF (@Counter > 150) AND (@Counter <= 300) " +
    			    "	     BEGIN " +
    			    "		SET @Counter0To50 = 50; " +
    			    " 		SET @Counter50To150 = 100; " +
    			    " 		SET @Counter150To200 = @Counter - 150; " +
    			    " 		SET @CounterOver200 = 0; " +
    			    " 	     END; " +
    
    			    " 	IF @Counter > 300 " +
    			    "        BEGIN " +
    			    " 		SET @Counter0To50 = 50; " +
    			    " 		SET @Counter50To150 = 100; " +
      			    " 		SET @Counter150To200 = 100; " +
    			    " 		SET @CounterOver200 = @Counter - 300; " +
    			    "        END;" +
    
    			    " 	SET @First50 = @Counter0To50 * 0.7675; " +
    			    " 	SET @FiftyTo150 = @Counter50To150 * 0.6248; " +
    		" 	SET @OneFiftyTo200 = @Counter150To200 * 0.5825; " +
    			    " 	SET @Over200 = @CounterOver200 * 0.5037; " +
    
    		" SET @TotalCharge = @BaseCharge + @First50 + @FiftyTo150 + " +
    			    " 	@OneFiftyTo200 + @Over200; " +
    
    			    " 	RETURN @TotalCharge; " +
    			    "END;";
    
        using (SqlConnection connection = new SqlConnection(strConnection))
        {
     	SqlCommand command =
    		new SqlCommand(CreateFunction, connection);
    
    	connection.Open();
    	command.ExecuteNonQuery();
    
    	MessageBox.Show(
    	    "A function named \"EvaluateInvoice\" has been created.");
        }
    }
  2. Change the code of the Evaluate button as follows:
     
    private void btnEvaluate_Click(object sender, EventArgs e)
    {
        int StartCounter = 0, EndCounter = 0;
        int Consumption;
    
        try
        {
    	StartCounter = int.Parse(txtCounterLastMonth.Text);
        }
        catch (FormatException)
        {
    	MessageBox.Show("Invalid Start Counter");
        }
    
        try
        {
    	EndCounter = int.Parse(txtCounterThisMonth.Text);
        }
        catch (FormatException)
        {
    	MessageBox.Show("Invalid End Counter");
        }
    
        if (StartCounter > EndCounter)
        {
    	MessageBox.Show("Invalid Values");
    	return;
        }
    
        Consumption = EndCounter - StartCounter;
    
        string strConnection =
    	"Data Source=(local);" +
    	"Database='UtilityCompany1';" +
    	"Integrated Security=yes;";
        string ExecuteFunction = "DECLARE @Difference int " +
    		"SET @Difference = " + Consumption.ToString() +
    		"SELECT dbo.EvaluateInvoice(@Difference);";
    
        using (SqlConnection connection = new SqlConnection(strConnection))
        {
    	SqlCommand command =
    		new SqlCommand(ExecuteFunction, connection);
    
    	connection.Open();
    	SqlDataReader rdr = command.ExecuteReader();
    
    	txtConsumption.Text = Consumption.ToString();
    
    	while (rdr.Read())
    		txtAmountDue.Text = rdr[0].ToString();
    
    	rdr.Close();
        }
    }
  3. Execute the application
  4. Enter some values for the start and end counter
  5. Click Evaluate. Here is an example:
     

    Creating and Calling a Function with Argument
  6. Close the form and return to your programming environment
  7. On the form, double-click the Database button and change the code of its event as follows:
     
    private void btnDatabase_Click(object sender, EventArgs e)
    {
        string strConnection =
            "Data Source=(local);Integrated Security=yes";
    
        using (SqlConnection connection = new SqlConnection(strConnection))
        {
            SqlCommand command =
    	    new SqlCommand("DROP DATABASE UtilityCompany1;",
    			   connection);
    
    	connection.Open();
        	command.ExecuteNonQuery();
    
            MessageBox.Show(
    	    "The UtilityCompany1 database has been deleted.");
        }
    }
  8. Execute the application
  9. Click the Create button
  10. Close the form and return to your programming environment

Lesson Summary

 

Exercises

  1. Write a function named ProcessPayroll1 that takes the number of hours worked in a week. Then the function returns a value that represents overtime. If the employee worked less than 40 hours, there is no overtime. If the employee worked for more than 40 hours, the number over 40 is considered overtime
  2. Write a function named GetWeekdayName that, when given a date, can find and display the name of the week for that date
  3. Write a function named GetNumberDays that takes two dates and returns the number of days between them
  4. Write a function named AddNumberDays that takes a date and an integer, then it returns the date added that number
 

Published on Monday 27 December 2007

 

Previous Copyright © 2007 FunctionX, Inc. Home