Functions Fundamentals

Introduction

Function Creation Fundamentals

In Transact-SQL, the primary formula of creating a function is:

CREATE FUNCTION FunctionName()

We mentioned already that, in SQL, a function is created as an object. As such, it must have a name. In our lessons, here are the rules we will use to name our functions:

• A name will start with either an underscore or a letter. Examples are _n, act, or Second
• After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are _n24, act_52_t
• A name will not include special characters such as !, @, #, $, %, ^, &, or *
• We will avoid using spaces in a name, with few exceptions 
• If the name is a combination of words, each word will start in uppercase. Examples are DateHired, _RealSport, or DriversLicenseNumber

For a function to be useful, it must produce a result. This is also said that the function returns a result or a value. When creating a function, you must specify the type of value that the function would return. To provide this information, after the name of the function, type the RETURNS keyword followed by a definition for a data type. Here is a simple example:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)

After specifying the type of value that the function would return, you can create a body for the function. The body of a function starts with the BEGIN and ends with the END keywords. Here is an example:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
BEGIN

END

Optionally, you can type the AS keyword before the BEGIN keyword:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN

END

Between the BEGIN and END keywords, which is the section that represents the body of the function, you can define the assignment the function must perform. After performing this assignment, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by an expression. A sample formula is:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN
    RETURN Expression
END

Here is an example

CREATE FUNCTION GetFullName()
RETURNS varchar(100)
AS
BEGIN
	RETURN 'Doe, John'
END

1. In the Object Explorer, right-click RealEstate1 and click New Query...
2. To create a function, type the following statement:
    

   CREATE FUNCTION CalculateWeeklySalary() RETURNS Decimal(8, 2) AS BEGIN RETURN 880.44 END; GO

3. To execute the statement, on the SQL Editor toolbar, click the Execute button
4. To save the file that contains the code of the function, on the Standard toolbar, click the Save button
5. Type Calculate as the name of the file
6. Click Save
7. In the Object Explorer, expand the RealEstate1 node, expand Programmability. Expand Functions. And expand Scalar-Valued Functions. Notice the presence of the CalculateWeeklySalary node

After a function has been created, you can use the value it returns. Using a function is also referred to as calling it. To call a function, you must qualify its name. To do this, type the name of the database in which it was created, followed by the period operator, followed by dbo, followed by the period operator, followed by the name of the function, and its parentheses. The formula to use is:

DatabaseName.dbo.FunctionName()

Because a function returns a value, you can use that value as you see fit. For example, you can use either PRINT or SELECT to display the function's value in a query window. Here is an example that calls the above Addition() function:

PRINT Exercise.dbo.GetFullName();

As an alternative, to call a function, in the Object Explorer, right-click its name, position the mouse on Script Function As, SELECT To, and click New Query Editor Window.

1. In the Object Explorer, right-click RealEstate1 and click New Query
2. To execute the function we just created, execute the following statement:
    

   PRINT RealEstate1.dbo.CalculateWeeklySalary(); GO

3. To specify a column name for the returned value of a function, change the function as follows and execute it:
    

   SELECT RealEstate1.dbo.CalculateWeeklySalary() AS [Weekly Salary]; GO

4. To save the current window, on the toolbar, click the Save button

Because a function in Transact-SQL is treated as an object, it may need maintenance. Some of the actions you would take include renaming, modifying, or deleting a function.

If you create a function and execute it, it is stored in the Scalar-Valued Functions node with the name you gave it. If you want, you can change that name but keep the functionality of the function.

To rename a function, in the Object Explorer, right-click it and click Rename. Type the desired new name and press Enter.

If you create a function and decide that you don't need it any more, you can delete it.

To delete a function in the Object Explorer, locate the function in the Functions section, right-click it and click Delete. The Delete Object dialog box would come up. If you still want to delete the function, click OK; otherwise, click Cancel.

To programmatically delete a function:

• In a query window, type DROP FUNCTION followed by the name of the function and execute the statement
• In the Object Explorer, right-click the name of the function, position the mouse on Script Function As, DROP To, and click New Query Editor Window
• Open a new query window associated with the database that contains the function. Display the Templates Explorer and expand the Function node. Drag the Drop Function node and drop it in the empty query window

1. In the Object Explorer, under the Scalar-Valued Functions node, right-click dbo.CalculateWeeklySalary and click Delete
2. In the Delete Object dialog box, click OK

As mentioned already, in the body of the function, you define what the function is supposed to take care of. As a minimum, a function can return a simple number, typed on the right side of the RETURN keyword. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
    RETURN 1
END

You can also declare new variables in the body of the function to help in carrying the assignment. A variable declared in the body of a function is referred to as a local variable. Once such a variable has been declared, it can be used like any other variable. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
    DECLARE @Number1 int
    SET @Number1 = 588
    RETURN @Number1 + 1450
END

1. In the Calculate query window, change the code as follows:
    

   CREATE FUNCTION CalculateWeeklySalary() RETURNS Decimal(8, 2) AS BEGIN DECLARE @HourlySalary Decimal(8, 2), @WeeklyHours Real, @FullName varchar(100); SET @HourlySalary = 24.15; SET @WeeklyHours = 42.50; RETURN @HourlySalary * @WeeklyHours END; GO

2. Press F5 to execute the statement
3. To call the function, select and delete the code. Replace it with the following:
    

   SELECT RealEstate1.dbo.CalculateWeeklySalary() AS [Weekly Salary]; GO

4. Execute the code by pressing F5

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.

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

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 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:

1. In the Object Explorer, under the Scalar-Valued Functions node, right-click dbo.CalculateWeeklySalary and click Delete
2. In the Delete Object dialog box, click OK
3. To add arguments, change the code of the Calculate() function as follows:
    

   CREATE FUNCTION CalculateWeeklySalary(@WeeklyHours Decimal(6,2), @HourlySalary SmallMoney) RETURNS Decimal(8, 2) AS BEGIN DECLARE @Weekly SmallMoney SELECT @Weekly = @WeeklyHours * @HourlySalary RETURN @Weekly END; GO

4. Press F5 to create the function
5. Delete the code in the window and replace it with the following:
    

   DECLARE @Hours Decimal(5,2), @Hourly SmallMoney SELECT @Hours = 42.50 SELECT @Hourly = 18.62 SELECT 'Hermine Singh' As [Employee Name], @Hours As [Weekly Hours], @Hourly As [Hourly Salary], LockmanRealEstate.dbo.CalculateWeeklySalary(@Hours, @Hourly) AS [Weekly Salary]; GO

6. Press F5 to execute the statement
    
   
7. Close the query window without saving the file
8. In the Object Explorer, under the Databases node, right-click RealEstate1 and click Delete
9. In the dialog box, click OK