Creating User-Defined Functions

Introduction

The Creating User-Defined Functions module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Working with scalar functions
• Using table-valued functions

Lab time: It will take approximately 30 minutes to complete this lab.

Exam Objectives

Three exam objectives are covered in this lab.

• Demonstrate how to create a scalar function using a Transact-SQL statement
• Demonstrate how to create an inline table-valued function using a Transact-SQL statement
• Demonstrate how to create a multi-statement table-valued function using a Transact-SQL statement

Exercise 1 - Working with Scalar Functions

A function is a database object that has a set of SQL statements. It accepts a parameter, performs an action, and returns the result. Functions can be used in constraints, computed columns, JOIN, WHERE clauses and so on. The result can consist of a single value or a table depending on the type of function. The functions that return a single value are known as scalar functions. The functions that return a table are called table-valued functions.

The scalar function returns a single value of the data type specified in the RETURNS clause. You can use scalar functions when you want the function to perform calculations on the fields in the table and return a single value. For example, you can use the scalar function if you want to calculate the average sales in a week, the total quantity of a product sold in a month and so on.

The syntax to create a scalar function is as follows:

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> ( -- Add the parameters for the function here <@Param1, sysname, @p1> <Data_Type_For_Param1, , int> ) RETURNS <Function_Data_Type,, int> AS BEGIN --Declare the return variable here DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int> --function body -- Return the result of the function RETURN <@ResultVar, sysname, @Result> END

In this exercise, you will learn to create scalar functions.

Exercise 2 - Using Table-Valued Functions

Table-Valued Function (TVF) returns an entire table as result. The table-valued function statement has three parts, which are as follows:

• RETURNS clause: It defines the local return variable to retrieve the table returned by the function.
• Function body: It inserts records in the return variable as per the condition specified.
• RETURN statement: It returns the resultant table as the output of the function.

TVFs can be reused in query statements.

There are two types of table-valued functions and they are as follows:

Inline TVFs

The syntax of an inline TVF is as follows:

1. The syntax of an inline TVF is as follows:

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> (
-- Add the parameters for the function here <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, <@param2, sysname, @p2> <Data_Type_For_Param2, , char> ) RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT 0 ) GO

• Multi-Statement TVFs

1. The syntax of a multi-statement TVF is as follows:

CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> ( -- Add the parameters for the function here <@param1, sysname, @p1> <data_type_for_param1, , int>, <@param2, sysname, @p2> <data_type_for_param2, , char> ) RETURNS <@Table_Variable_Name, sysname, @Table_Var> TABLE ( -- Add the column definitions for the TABLE variable here <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, <Column_2, sysname, c2> <Data_Type_For_Column2, , int> ) AS BEGIN -- Fill the table variable with the rows for your result set RETURN END

In this exercise, you will learn to create and implement both the types of table-valued functions.