How to create a Multi-Statement Table valued function

In this session, we will learn about creating and calling multi-statement table-valued functions. Additionally, we will explore the differences between inline and multi-statement table-valued functions. Before proceeding with the session, I highly recommend reading these two articles.

1. Scalar functions
2. Inline table-valued functions Basic Concept

How to create a multi-statement table valued function

Multistatement table-valued functions are quite similar to inline table-valued functions, with only a few distinctions between them.

First, let’s examine an example of creating both an inline and a multistatement table-valued function, and then we’ll explore the variances between them. I have a table named TblCustomers, which contains columns for Id, Email, PhoneNo, FirstName, LastName, and CountryId.

Table Script

CREATE TABLE [dbo].[TblCustomers](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Email] [nvarchar](max) NOT NULL,
    [PhoneNo] [nvarchar](14) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [CountryId] [int] NULL,
 )
GO
SET IDENTITY_INSERT [dbo].[TblCustomers] ON 
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (1, '[email protected]', '2827528619', 'David', 'Kattah', 1)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (17, '[email protected]', '0557161755', 'Mame', 'Adjei', 90)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (18, '[email protected]', '0553474280', 'Dzifa', 'Adabla', 90)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (19, '[email protected]', '0244072176', 'Phyllis ', 'Adu', 90)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (20, '[email protected]', '0545196619', 'David', 'Nikoi', 90)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (21, '[email protected]', '98979495', 'Ankiish', 'Thapliyal', 90)
GO
SET IDENTITY_INSERT [dbo].[TblCustomers] OFF
GO

Now, what we want to do is create inline and multistatement table-valued functions that provide the same output as shown in the image below. We only require the name and email fields.

Inline function

Create Function InlineFn_GetAllUsers()
Returns Table
as
RETURN (Select FirstName, Email
from TblCustomers)

We are creating an inline table-valued function, which we have extensively discussed in the previous post dedicated to inline table-valued functions.

To create the function, we use the `CREATE FUNCTION` syntax followed by the function name and the `RETURNS` keyword. We know that a function can take parameters and should return a value.

In this case, our function does not take any parameters and returns a table. Following the `RETURNS` keyword, we specify the structure of the table to be returned. Then, using the `AS` keyword, we specify that the function will return whatever the select statement retrieves.

In this function, the select statement retrieves the `name` and `email` fields from the `TblCustomers` table. Therefore, the function returns the results of this select statement, which includes the `name` and `email` columns from the `TblCustomers` table.

Thus, this is an example of an inline table-valued function.

1. And if you look at the inline table-valued function after returns, you have only the table keyword. You’re not specifying the structure of the table that gets returned from this inline table-valued function.
2. And another thing to note here is that this function doesn’t have a begin and end clause.

Create Multi-statement table valued function

Create Function MultiFn_GetAllUsers()
Returns @Table Table (FirstName nvarchar(50), Email nvarchar(100))
as
Begin
Insert into @Table
Select FirstName,Email
From TblCustomers

Return
End

Let's examine the multi-statement table-valued function. Regardless of whether it is an inline table-valued function, scalar function, or multi-statement table-valued function, you always use the `CREATE FUNCTION` statement to define it.

In this case, we use the `CREATE FUNCTION` statement followed by the function name, which here is `MultiFn_GetAllUsers`. Then, we specify the `RETURNS @table` syntax. Here, `@table` represents a table variable.

When creating a table variable, we must define its structure, including the column names and their respective data types. Therefore, we specify the name of the columns and their data types within the table variable definition.

@Table Table (FirstName nvarchar(50), Email nvarchar(100))

You are saying, I am returning a table with this structure which has got FirstName and Email.

Multi-statement Table Valued Function vs Inline Table Valued Function

Here, we are populating the table variable by inserting data into it. We use the `INSERT INTO` statement to accomplish this task. Specifically, we want to insert the `name` and `email` fields from the `TblCustomers` table into the `@table` variable.

So, for each record in the `TblCustomers` table, we retrieve the `name` and `email` columns and insert them as rows into the `@table` variable.

as
Begin


Insert into @Table


Select FirstName,Email
From TblCustomers


Return


End

If you examine the table variable, you'll notice it has columns for `name` and `email`. By inserting data into this table variable and then specifying `return`, the function will return this table.

This means that whatever data has been inserted into the `@table` variable will be returned to the caller of the function. They will receive the data contained within this table.