Home

Introduction to Functions

 

Functions Fundamentals

 

Introduction

A function is a relatively small task that should be performed aside but can be accessed any time to give a result. Unlike most other languages, a function in Transact-SQL is always created publicly (meaning it is automatically made available to any section of the database) and globally. As such, it is stored in its own file.

After creating a function in Transact-SQL, it becomes part of the User-Defined Functions node in the Enterprise Manager. Therefore, to view the structure of an existing function, in Enterprise Manager, locate the database and expand it. Click the User-Defined Functions node in the left pane and, in the right pane, double-click the name of the function.

Practical Learning Practical Learning: Introducing Functions

  1. Start the SQL Server Enterprise Manager
  2. Expand the Microsoft SQL Servers, the SQL Server Group, and the name of the server nodes
  3. To create a new database, right-click Databases and click New Database...
  4. In the Name text box, type LockmanRealEstate and click OK
  5. Expand the Databases followed by the LockmanRealEstate nodes
 

Function Creation in Enterprise Manager

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()
 

The Name of a Function

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

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.

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

    RETURN Expression
END
 

Practical Learning 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 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 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 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 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
 

Previous Copyright © 2004-2005 FunctionX, Inc. Next