Introduction to Functions

There are two main ways you can create a function: using Enterprise Manager or SQL Query Analyzer. In the MMC, to create a function, in the desired database, right-click the User-Defined Functions node and click New User Defined Function... You would be presented with a dialog box that displays a formula in which you can make the necessary change:

As you can see, the primary formula of creating a function is:

CREATE FUNCTION FunctionName()

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

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

END

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

END

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

    RETURN Expression
END

Practical Learning: Creating Functions

1. In the left frame and under the LockmanRealEstate node, right-click User Defined Functions and click New User Defined Function...
2. To create a function, replace the statement with the following:
    

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

3. Click Check Syntax and click OK
4. Click OK
5. In the left frame, click the User Defined Functions node and notice that the right frame displays the name of the new function

Function Creation in Transact-SQL

In Transact-SQL, to create a function, you start with the CREATE FUNCTION expression followed by a name and the same syntax as seen above:

CREATE FUNCTION FunctionName()

Just like the User-Defined Function Properties provides you with a template made of placeholders you can change to customize the function, to assist you with creating a function, the SQL Query Analyzer provides a wizard you can use. This also creates placeholders you can use. One of the advantages of these wizards is that they provide sections of code that allow you to perform some checking.

Practical Learning: Creating Functions

1. To open the SQL Query Analyzer, on the toolbar of the SQL Server Enterprise Manager, click Tools -> SQL Query Analyzer
2. To create a function from a template, on the main menu of SQL Query Analyzer, click File -> New...
3. In the New dialog box, double-click Create Function
4. Click the Create Scalar Function icon
    
   
5. Click OK
6. In the combo box of the toolbar of SQL Query Analyzer, make sure that LockmanRealEstate is selected; otherwise select it.
   To complete the function, change the statement as follows:
    

   -- ============================================= -- Function: GetFullName -- The purpose of this function is to -- produce an employee's full name from -- first and last names -- ============================================= IF EXISTS (SELECT * FROM sysobjects WHERE name = N'GetFullName') DROP FUNCTION GetFullName GO CREATE FUNCTION GetFullName() RETURNS varchar(100) AS BEGIN RETURN 'Doe, John' END GO

7. To execute the statement, on the toolbar, click the Execute Query button
8. To save the file that contains the code of the function, on the toolbar, click the Save button
9. Type GetFullName as the name of the function. Notice where the file will be located (it should be My Documents)
10. Click Save

Function Calling

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 specify the database in which it was created and the dbo factor. 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 the Query Window. Here is an example that calls the above Addition() function:

PRINT Variables1.dbo.Addition()

Practical Learning: Calling a Function

1. On the main menu of SQL Query Analyzer, click File -> New...
2. In the New dialog box, accept Blank Query Window and click OK
3. To execute the function we just created, execute the following statement:
    

   SELECT LockmanRealEstate.dbo.GetFullName() GO

4. To execute the first function we created, execute the following statement:
    

   SELECT LockmanRealEstate.dbo.CalculateWeeklySalary() GO

5. To specify a column name for the returned value of a function, execute the following statement:
    

   SELECT LockmanRealEstate.dbo.GetFullName() As [Full Name] GO

6. To save the current window, on the toolbar, click the Save button
7. Type Execution as the name of the function. Notice where the file will be located (it should be My Documents)
8. Click Save

Function Maintenance

Introduction

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

Function Copy

You can copy the code of a function as text and paste that code in a separate document. To do this, in the Enterprise Manager, you can right-click the function in its User Defined Functions node and click Copy. Again, it is the code of the function that you are copying, not the name of the function. Alternatively, still in the Enterprise Manager, you can first double-click the function or right-click it and click Properties. Then, select the the text in the Text memo, right-click it and click Copy.

To copy a function in the SQL Query Analyzer, display its code, select its text, right-click the text and click Copy.

After copying a function, you can paste it in a document such as one from Notepad or any application that accepts pasted text.

Function Deletion

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

To delete a function in the Enterprise Manager, locate the function in the User-Defined Functions node. Right-click it and click Delete.

To programmatically delete a function, in the Query Window, type DROP FUNCTION followed by the name of the function and execute the statement.

Function Modification and Update 

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

Practical Learning: Declaring Local Variables

1. On the main menu of SQL Query Analyzer, click Window -> Query - ...\GetFullName.sql
2. To declare local variables, change the file as follows:
    

   -- ============================================= -- Function: GetFullName -- The purpose of this function is to -- produce an employee's full name from -- first and last names -- ============================================= IF EXISTS (SELECT * FROM sysobjects WHERE name = N'GetFullName') DROP FUNCTION GetFullName GO CREATE FUNCTION GetFullName() RETURNS varchar(100) AS BEGIN DECLARE @FirstName varchar(40), @LastName varchar(40), @FullName varchar(100) SET @FirstName = 'William' SET @LastName = 'Curtis' SET @FullName = @LastName + ', ' + @FirstName RETURN @FullName END GO

3. Press F5 to execute the statement
4. On the main menu, click Window -> Query - ...\Execution.sql
5. To call the function, execute the following statement
    

   SELECT LockmanRealEstate.dbo.GetFullName() As [Full Name] GO

Function 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 an argument. A function can also take more than one argument. Therefore, when you create a function, you also decide whether your function would take one or more arguments and what those arguments, if any would be.

An Argumentative 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 argument. The name of the argument 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 an argument, in the body of the function, you can use the argument 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 Function With Argument

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

A Function With Various Arguments

Instead of only one argument, you can also create a function that takes more than one argument. 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 arguments as if you already knew their value. You can also declare local variables and involve them with arguments 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

When calling a function that takes more than one argument, in the parentheses of the function, provide a value for each argument, 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(8,3),
        @Nbr2 Decimal(4,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 Learning: Creating Functions With Arguments

1. To add arguments, change the code of the GetFullName() function as follows:
    

   -- ============================================= -- Function: GetFullName -- The purpose of this function is to -- produce an employee's full name from -- first and last names -- ============================================= IF EXISTS (SELECT * FROM sysobjects WHERE name = N'GetFullName') DROP FUNCTION GetFullName GO CREATE FUNCTION GetFullName(@FirstName varchar(40), @LastName varchar(40)) RETURNS varchar(100) AS BEGIN DECLARE @FullName varchar(100) SET @FullName = @LastName + ', ' + @FirstName RETURN @FullName END GO

2. Press F5 to execute the statement
3. To call this function, access the Execution window and change its contents as follows:
    

   DECLARE @FName varchar(40), @LName varchar(40) SET @LName = 'Thomas' SET @FName = 'Patrick' SELECT LockmanRealEstate.dbo.GetFullName(@FName, @LName) As [Complete Name] GO

4. Switch to the Enterprise Manager.
   To add arguments to the function, change the code of the CalculateWeeklySalary 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

5. Click Check Syntax and click OK
6. Switch to SQL Query Analyzer and, on the toolbar, click the New Query button
7. In the empty window, to call the function, type 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

8. Press F5 to execute the statement
    
   
9. Close the SQL Query Analyzer without saving anything