SQL Server - Lesson 22: Records and Functions

	Records and Functions

Data Entry and Functions

Introduction

You can involve a function during data entry. As an example, you can call a function that returns a value to assign that value to a column. You can first create your own function and use it, or you can use one of the built-in functions.

Using Functions

In order to involve a function with your data entry, you must have and identity one. You can use one of the built-in functions of Transact-SQL. You can check one of the functions we reviewed in Lesson 8. Normally, the best way is to check the online documentation to find out if the assignment you want to perform is already created. Using a built-in function would space you the trouble of getting a function. For example, imagine you have a database named AutoRepairShop and imagine it has a table used to create repair orders for customers:

CREATE TABLE RepairOrders
(
  RepairID int Identity(1,1) NOT NULL,
  CustomerName varchar(50),
  CustomerPhone varchar(20),
  RepairDate datetime2
);
GO

When performing data entry for this table, you can let the user enter the customer name and phone number. On the other hand, you can assist the user by programmatically entering the current date. To do this, you would call the GETDATE() function. Here are examples:

INSERT INTO RepairOrders(CustomerName, CustomerPhone, RepairDate)
	    VALUES(N'Annette Berceau', N'301-988-4615', GETDATE());
GO
INSERT INTO RepairOrders(CustomerPhone, CustomerName, RepairDate)
	    VALUES(N'(240) 601-3795', N'Paulino Santiago', GETDATE());
GO
INSERT INTO RepairOrders(CustomerName, RepairDate, CustomerPhone)
	    VALUES(N'Alicia Katts', GETDATE(), N'(301) 527-3095');
GO
INSERT INTO RepairOrders(RepairDate, CustomerPhone, CustomerName)
	    VALUES(GETDATE(), N'703-927-4002', N'Bertrand Nguyen');
GO

You can also involve the function in an operation, then use the result as the value to assign to a field. You can also call a function that takes one or more arguments; make sure you respect the rules of passing an argument to a function when calling it.

Multi-Statement Table-Valued Functions

Introduction

All of the functions we have created so far returned 0 or a single value that was one of the data types supported by Transact-SQL. A table-valued function is a function that returns a table. A multi-statement table-valued function is a function that creates a table, optionally fills it up with the desired records, and then returns the table.

Creating a Table-Valued Function

To create a function that returns a table, you have various options. In the Object Explorer, expand the database that will own the function, expand the Programmability node, and expand Functions. Right-click Table-Valued Functions and click New Multi-Statement Table-Valued Function...

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
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
GO

As an alternative, open a new Query window. In the Templates explorer, expand Function, drag Create Multi-Statement Function (New Menu):

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
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
GO

The primary formula to create a multi-statement table-valued function is:

CREATE FUNCTION FunctionName()
RETURNS @TableName TABLE (Table's Columns)
AS
BEGIN
    RETURN;
END;

You start with the CREATE FUNCTION expression followed by a name for the function and its parentheses. As seen in Lesson 7, after the parentheses, type the RETURNS keyword. Since you are actually creating a table, follow the RETURNS keyword with the @ operator, followed by the desired name of the table, followed by the TABLE keyword, and its parentheses.

Create the columns of the table in the parentheses. Each column is created using the formula:

ColumnName DataType Options

Each column follows the necessary options: name, data type, optional primary key, optional nullity, optional unique, optional check, or optional expression.

After the parentheses that contain the columns, type AS, BEGIN, RETURN and END, preferably on different lines for better readability. Here is an example that creates a function that internally creates a table:

USE Exercise;
GO

CREATE FUNCTION GetStates()
RETURNS @States TABLE
(
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    RETURN;
END;
GO

Data Entry on a Table-Valued Function

When creating the function, if you want to add records to the table, before the RETURN keyword, use the INSERT or INSERT INTO statement(s) as necessary. Here is an example:

USE Exercise;
GO

CREATE FUNCTION GetStates()
RETURNS @States TABLE
(
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @States VALUES(N'DC', N'District of Columbia'),
			      (N'MD', N'Maryland'),
			      (N'PA', N'Pennsylvania'),
			      (N'VA', N'Virginia'),
			      (N'WV', N'West Virginia');
    RETURN;
END;
GO

Calling a Multi-Statement Table-Valued Function

After creating a multi-statement table-valued function, it is represented in the Table-Valued Functions node of the Object Explorer. To call the function, use a SELECT statement. To indicate that you want all records of the table, you can use the * operator. Here is an example:

USE Exercise;
GO

SELECT * FROM GetStates();
GO

This would produce:

Multi-Select Table-Valued Function

A multi-statement table-valued function is a type of table. It simply makes it easy to access the records of a table with one call. Because it is a table-type, you can access one, some, or all of its fields. To specify the fields you want to get, create a SELECT statement that holds the list of columns. Here is an example:

USE Exercise;
GO
SELECT LongName FROM GetStates();
GO

This would produce:

Inline Table-Valued Function

Of course you can also set conditions that the SELECT statement must follow. In the same way, you can include any of the statements we have already seen, or that we will see in other lessons, in the creation or call of an inline table-valued function.

Passing Arguments to a Multi-Statement Table-Valued Function

In its formula, we saw that a table-valued function had parentheses. In the parentheses, you can specify one or more arguments. Each argument is in the form of @, followed by a name, and a data type. Here is an example:

USE Exercise;
GO

CREATE FUNCTION GetStates(@IdentifyRegion nvarchar(40))
RETURNS @States TABLE
(
    RegionName nvarchar(40),
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @States VALUES(N'New England', N'CT', N'Connecticut'),
			      (N'New England', N'ME', N'Maine'),
			      (N'South Atlantic', N'DC', N'District of Columbia'),
			      (N'South Atlantic', N'MD', N'Maryland'),
			      (N'South Atlantic', N'PA', N'Pennsylvania'),
			      (N'South Atlantic', N'VA', N'Virginia'),
			      (N'South Atlantic', N'WV', N'West Virginia');
    RETURN;
END;
GO

When calling the function, you must provide a value for the argument(s). Here is an example:

USE Exercise;
GO

DECLARE @Region nvarchar(40);
SET @Region = N'South Atlantic';
SELECT ShortName, LongName, RegionName FROM GetStates(@Region);
GO

The argument of the function is created using any rule you know about arguments. For example, it can be created with a default value. Here is an example:

USE Exercise;
GO

CREATE FUNCTION GetStates(@IdentifyRegion nvarchar(40) = N'All')
RETURNS @States TABLE
(
	RegionName nvarchar(40),
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @States VALUES
	(N'New England', N'CT', N'Connecticut'), (N'New England', N'ME', N'Maine'),
	(N'New England', N'MA', N'Massachusetts'), (N'New England', N'NH', N'New Hampshire'),
	(N'New England', N'RI', N'Rhode Island'), (N'New England', N'VT', N'Vermont'),
	(N'Mid-Atlantic', N'NJ', N'New Jersey'), (N'Mid-Atlantic', N'NY', N'New York'),
	(N'Mid-Atlantic', N'PA', N'Pennsylvania'), (N'South Atlantic', N'DE', N'Delaware'),
	(N'South Atlantic', N'DC', N'District of Columbia'),
	(N'South Atlantic', N'FL', N'Florida'),	(N'South Atlantic', N'GA', N'Georgia'),
	(N'South Atlantic', N'MD', N'Maryland'),
	(N'South Atlantic', N'PA', N'Pennsylvania'), (N'South Atlantic', N'VA', N'Virginia'),
	(N'South Atlantic', N'WV', N'West Virginia'),
	(N'All', N'AL', N'Alabama'), (N'All', N'AK', N'Alaska'), (N'All', N'AZ', N'Arizona'),
	(N'All', N'AR', N'Arkansas'), (N'All', N'CA', N'California'),
	(N'All', N'CO', N'Colorado'), (N'All', N'CT', N'Connecticut'), 
	(N'All', N'DE', N'Delaware'), (N'All', N'DC', N'District of Columbia'),
	(N'All', N'FL', N'Florida'), (N'All', N'GA', N'Georgia'), (N'All', N'HI', N'Hawaii'),
	(N'All', N'ID', N'Idaho'), (N'All', N'IL', N'Illinois'), (N'All', N'IN', N'Indiana'),
	(N'All', N'IA', N'Iowa'), (N'All', N'KS', N'Kansas'), (N'All', N'KY', N'Kentucky'),
	(N'All', N'LA', N'Louisiana'), (N'All', N'ME', N'Maine'), (N'All', N'MD', N'Maryland'),
	(N'All', N'MA', N'Massachusetts'), (N'All', N'MI', N'Michigan'),
	(N'All', N'MN', N'Minnesota'), (N'All', N'MS', N'Mississippi'),
	(N'All', N'MO', N'Missouri'), (N'All', N'MT', N'Montana'), (N'All', N'NE', N'Nebraska'),
	(N'All', N'NV', N'Nevada'), (N'All', N'NH', N'New Hampshire'),
	(N'All', N'NJ', N'New Jersey'),	(N'All', N'NM', N'New Mexico'),
	(N'All', N'NY', N'New York'), (N'All', N'NC', N'North Carolina'),
	(N'All', N'ND', N'North Dakota'), (N'All', N'OH', N'Ohio'),
	(N'All', N'OK', N'Oklahoma'), (N'All', N'OR', N'Oregon'),
	(N'All', N'PA', N'Pennsylvania'), (N'All', N'RI', N'Rhode Island'),
	(N'All', N'SC', N'South Carolina'), (N'All', N'SD', N'South Dakota'),
	(N'All', N'TN', N'Tennessee'), (N'All', N'TX', N'Texas'), (N'All', N'UT', N'Utah'),
	(N'All', N'VT', N'Vermont'), (N'All', N'VA', N'Virginia'),
	(N'All', N'WA', N'Washington'),	(N'All', N'WV', N'West Virginia'),
	(N'All', N'WI', N'Wisconsin'), (N'All', N'WY', N'Wyoming');
    RETURN;
END;
GO

When calling the function, you can omit the value of the argument and type DEFAULT in its place holder:

USE Exercise;
GO

SELECT RegionName, ShortName, LongName FROM GetStates(DEFAULT);
GO

Otherwise, you can call the function and pass a value of your choice.

Table-Valued Functions and Conditions

When calling a multi-statement table-valued function, you can use a condition to restrict the list of records it produces. For example, you can use a WHERE condition to control the result. Here is an example:

USE Exercise;
GO

DECLARE @Region nvarchar(40);
SET @Region = N'South Atlantic';
SELECT LongName, ShortName FROM GetStates(@Region)
WHERE RegionName = @Region;
GO

This would produce:

Multi-Statement Table-Valued Functions

In the same way, you can use CASE, LIKE, BETWEEN, etc to refine the result.

Inline Table-Valued Functions

Introduction

Consider the following Employees table and its records:

USE Exercise;
Go

CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

BEGIN TRANSACTION AddEmployees

INSERT INTO Employees
VALUES(N'593705', N'Frank Somah', N'20061004', 26.15),
      (N'720947', N'Paul Handsome', N'20000802', 36.05)
GO
INSERT INTO Employees(EmployeeNumber, EmployeeName, HourlySalary)
VALUES(N'247903', N'Gina Palau', 18.85);
GO
INSERT INTO Employees
VALUES(N'595002', N'John Meah', N'20000212', 32.25);
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Clarice Simms', N'971403', N'20011112');
GO
INSERT INTO Employees
VALUES(N'928375', N'Chuck Stansil', N'20080628', 20.18),
      (N'792764', N'Orlando Perez', N'20000616', 12.95);
GO

COMMIT TRANSACTION AddEmployees;
GO

When you want to retrieve the records held by that table, you can write a SELECT statement made of too many sections. Imagine you plan to get the records many times, over and over. Instead of creating a new SELECT statement every time, you can create a function that gives you access to the records.

An inline table-valued function is a function that produces the records from a function using a SELECT statement. As opposed to an inline table-valued function, you cannot (directly) include a SELECT statement in a multi-statement table-valued function. The solution is to create the function as inline.

Creating an Inline Function

To create an inline table-valued function, you have various options. To have code generated for you:

In the Object Explorer, expand the database that will own the function. Expand Programmability. Expand Functions. Right-click Table-Valued Functions and click New Inline Table-Valued Function...

-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
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

In the Template Explorer, expand the Function node. Right-click Create Inline Function (New Menu) and click either Open or Edit
Open an empty Query window. In the Template Explorer, expand the Function node. Drag Create Inline Function (New Menu) and drop it in the Query window

Once the skeleton code has been generated for you, you can edit and customize it.

The basic formula to create an inline function is:

CREATE FUNCTION FunctionName(Argument(s))
RETURNS TABLE 
AS
RETURN
    SELECT 0

You start with the CREATE FUNCTION expression followed by a name for the function and its arguments. If the function takes one or more arguments, enter it(them) in the parentheses. If there is no argument, leave the parentheses empty,

The closing parenthesis is followed by the RETURNS TABLE expression. The TABLE keyword indicates that the function will return a list of columns and their records. This is followed by the AS and the RETURN keywords.

After the RETURN keyword, if necessary, declare some variable(s) and do what you want in the body of the function. Before the end of the function, you must create a SELECT statement that returns a table-type.

Here is an example:

USE Exercise;
GO

CREATE FUNCTION GetEmployees()
RETURNS TABLE 
AS
RETURN 
    SELECT * FROM Employees;
GO

Calling an Inline Table-Valued Function

After creating an inline table-valued function, you can access its records. To call such a function, you use a SELECT statement. For example, to indicate that you want all records of the table, you can use the * operator. Here is an example:

USE Exercise;
GO

SELECT * FROM GetEmployees();
GO

This would produce:

Inline Table-Valued Function

Because an inline table-valued function is a type of table, you can access one, some, or all of its fields using a SELECT statement. Here is an example:

SELECT EmployeeName, HourlySalary FROM GetEmployees();

Of course you can also set conditions that the SELECT statement must follow. Here is an example:

SELECT EmployeeNumber, EmployeeName  FROM GetEmployees()
WHERE HourlySalary >= 20.00;

In the same way, you can include any of the statements we have already seen, or that we will see in other lessons, in the creation or call of an inline table-valued function.