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.
The name of the parameter is preceded by the @ sign, like a variable. Here is an example: CREATE FUNCTION Administration.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 Administration.Addition(@Number1 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
RETURN @Number1 + 1450
END
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:
Instead of only one parameter, you can also create a function that takes more than one parameter. In this case, separate the parameters in the parentheses of the function with a comma. Here is an example: CREATE FUNCTION Administration.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 Administration.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 Exercise.Administration.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,
Exercise.Administration.Addition(@Nbr1, @Nbr2) AS Result
This would produce:
When a function with argument is called, a value must be passed for each argument. Here is an example of such a function: USE Exercise;
GO
CREATE FUNCTION CalculateTaxAmount(@Price money, @Rate decimal(6, 2))
RETURNS decimal(6, 3)
AS
BEGIN
DECLARE @ResultVar money
SELECT @ResultVar = @Price * @Rate / 100
RETURN @ResultVar
END
GO
Here are examples of calling the function: USE Exercise; GO PRINT dbo.CalculateTaxAmount(140.00, 5.75); PRINT dbo.CalculateTaxAmount(195.95, 5.75); PRINT dbo.CalculateTaxAmount(250.00, 7.55); PRINT dbo.CalculateTaxAmount(125.95, 5.75); GO This would produce: 8.05 11.27 18.88 7.24 In some cases, if the function is usually called with the same value for an argument, you can specify a default value for that argument. When such a function is called, you can omit the value of the argument. To specify a default value for an argument, in the parentheses of the function, after the name and data type of the argument, type =, followed by the desired value. Here is an example: USE Exercise;
GO
CREATE FUNCTION CalculateTaxAmount(@Price money, @Rate decimal(6, 2) = 5.75)
RETURNS decimal(6, 2)
AS
BEGIN
DECLARE @ResultVar money
SELECT @ResultVar = @Price * @Rate / 100
RETURN @ResultVar
END
GO
|
|
|||||||||||||||||||||
|
|
Practical
Learning: Introducing Functions
