A common table expression, or CTE, is a temporary
selection or other query operation of records from one or more tables. You
use it to get an idea of what the query operation would produce if performed
on a table. You can generate a CTE to create, select, merge, or delete
records.

There are two types of common table expressions:
recursive and non-recursive.

You start with the WITH keyword followed by a
name for the temporary set. The name must be different from any table that
will be used in the CTE's expression. Later, we will see the parameters
you can add after the name. After the name, type AS followed by
parentheses. In the parentheses, create a simple or composite SELECT
expression.

After the code that defines the CTE, that is, after
the AS(CTE_query_definition) expression, create a
SELECT statement that will produce the results.

To see a list of only available rooms from the CTE, change the
statement as follows:

USE CeilInn2;
GO
WITH BedRooms AS
(
SELECT * FROM SleepingRooms WHERE Available = 1
)
SELECT * FROM BedRooms
GO

Press F5 to see the result

A CTE With Parameters

To make sure you can externally control the results of a
CTE, you can pass a type of parameter to it. To do this, after the name of
the CTE and before the AS operator, add the parentheses and pass one
or more parameters, each represented by a name. The names of parameters must
be the exact same names of columns of the table(s) from which the CTE's
statement will be based. The number of columns must be the same as the
number of columns that will be involved in the final SELECT
statement.

In the body of the CTE, use the parameter(s) as you
wish. For example, you can involve the parameter(s) in a condition in the
CTE.

Practical
Learning: Passing Parameters to a CTE

To pass some parameters to the CTE, delete everything in the Query
window and replace it with:

In all of our SELECT expressions so far, considered
all records of the intended table. In some cases, you can set a condition
that would select only some records. You can use the condition in one or
both SELECT statements.

Recursive Common Table Expressions

A recursive common table expression is a CTE that can
contain more than one SELECT statement. In the body of the CTE, you
can create as many SELECT statements as you want but those statements
must be joined. To join them, you can use a UNION, UNION ALL,
or MERGE operator.

A non-recursive common table expression is a CTE that
can be followed by only one SELECT, INSERT, UPDATE, or
DELETE statement that involves a column from inside the CTE's body.

Topics on Common Table Expresions

In our recursive CTE expressions so far, considered
all records of the intended table. In some cases, you can set a condition
that would select only some records before merging them. You can use the condition in one or
both SELECT statements.

Fundamentals of 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...

You start with the CREATE FUNCTION expression
followed by a name for the function and its parentheses. As seen already,
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:

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:

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:

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:

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:

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:

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

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.

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:

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 a condition that the
SELECT statement must follow. Here is an example: