How to create a multi-statement table valued function

In this session, we learn about creating and calling multi-statement table-valued functions. And finally, we’ll also look at the differences between inline and multi-statement table-valued functions. Before continuing with the session, I strongly recommend reading these two articles.

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

How to create a multi-statement table valued function

Multis Statement Table-valued functions are very much similar to inline table-valued functions, with very few differences between them.

First, let’s look at an example of creating an inline and multi-statement table-valued function, and then we’ll find the differences between them. I have this TblCustomers table which is got Id, Email, PhoneNo, FirstName, LastName, and countryId columns.

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, 'david@virtualsolutionsgh.net', '2827528619', 'David', 'Kattah', 1)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (17, 'mamestastes@gmail.com', '0557161755', 'Mame', 'Adjei', 90)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (18, 'dzifa.adabla@gmail.com', '0553474280', 'Dzifa', 'Adabla', 90)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (19, 'fylissa@yahoo.com', '0244072176', 'Phyllis ', 'Adu', 90)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (20, 'nikoidave@gmail.com', '0545196619', 'David', 'Nikoi', 90)
GO
INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (21, 'thapliyalankiish958@gmail.com', '98979495', 'Ankiish', 'Thapliyal', 90)
GO
SET IDENTITY_INSERT [dbo].[TblCustomers] OFF
GO

Now what we want to do is we want to create inline and multi-statement table-valued functions that give us the same output that you can see on the below image. I just want the name and Email.

Inline function

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

we are creating an inline table-valued function and we have spoken about inline table-valued functions extensively in inline table-valued functions post.

so to create function we have used create function and then function name, returns keyword.we know that a function can take parameters and it should return a value.

so our function is not taking any parameters and it’s returning a table. And then we have ‘As’ you would return whatever is the select statement returning, here The select statement is selecting name and email from table TblCustomers table.

So whatever the select statement is retrieving from TblCustomers table that is being returned by the our function.

so this is 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 look at the multi-statement table value function. whether if it is inline table-valued function or scalar function or multi-statement table-valued function, You always use create function statement to create a function. so create function and then the name of the function here MultiFn_GetAllUsers.

And then I’m saying returns @table. So I’m creating a table variable . so @table is the name of the variable and its data type is a table. And obviously, if you’re creating a table variable, a table variable has to have, the column names and the data types.

So we are creating a table and specify the name of the column and its data type.

@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

1. In inline table-valued function. You don’t specify the structure of the table, but in multi-statement table-valued function, you do specify the structure of the table.
2. And then ‘As’ and then you have this begin and end block. But if you look at the Inline table-valued function, you don’t have that block,And within that begin an end block, you have the body of the function.
3. You can use an inline table-valued function as in updated, and deleted from – just like we can use a view with Similar limitations i.e can’t update functions using aggregates, can’t update calculated columns, etc. But you can use the Multi-statement Table function.
4. Now if talk about the performance SQL Server uses an inline table-valued function as a view and a multi-statement table-valued function like a stored procedure.

   Whenever we use an inline table-valued function in the outer query, the query processor read the User defined-function definition and creates an execution plan that uses the underlying objects table, using the indexes on the table.

   In the case of a multi-statement table-valued function, an execution plan is generated for the function itself and stored in the execution plan cache when the function is executed the first time.

   If we use multi-statement table-valued functions in larger SQL queries then the optimizer does not know what the multi-statement function will return, So it assumes that the function will return a single row and that the returns of the function will be accessed by using a table scan against a table with a single row instead of using indexes.

   So multi-statement table-valued functions can perform worst is in case of a large number of rows.

what are you doing here? Here you are inserting data into this table variable. You’re saying insert into this table variable.

So what do you want to insert into this table variable. You want to insert the name and email from the TblCustomers table. So you are retrieving all these records from TblCustomers tables. You’re basically retrieving name and email columns and then inserting those rows into @table variable.

as
Begin


Insert into @Table


Select FirstName,Email
From TblCustomers


Return


End

If you look at the table variable, it has got the name and email columns.so you are inserting that into this table and finally, you’re saying return

So when this function encounters this return Keywood, what’s going to happen? This function will return this table.

whatever you have inserted into this at table variable gets returned to the caller who was called to your function, he will get the data that is present in this table.

The post How to create a multi-statement table valued function appeared first on Software Development | Programming Tutorials.