How to Create Inline Table-Valued functions in SQL server

In this part of the session, we will focus on creating and calling inline table-valued functions, which are used to return a table as output. Before proceeding, I recommend reading the previous post on Scalar User-Defined Functions to gain a basic understanding.

Let's delve into the concept of inline table-valued functions. Unlike scalar functions that return a single value, inline table-valued functions return a table.

To illustrate, consider the TblUsers table containing columns like ID, Name, Email, Password, Gender, DOB, and ContactNo. Our objective is to create a function that produces the output shown in the image below.

Userbygender

The function is intended to filter users based on their gender. For instance, when the gender parameter is set to 'male', only male users should be returned. Similarly, when the gender parameter is set to 'female', only female users should be returned.

How to create and call inline table-valued function

To do that we use the create function statement as we have used it to create scalar function.

The syntax for creating an inline table-valued function

CREATE FUNCTION Your_Function_Name(@Parameter1 DataType, @Parameter2 DataType..., @Parametern DataType)
RETURNS TABLE
AS
RETURN (Select_query_Statement)

Inline table-valued function Example

CREATE FUNCTION [dbo].[UsersByGender](@UserGender nvarchar(10))
RETURNS TABLE
AS
RETURN (Select Id, Name,Email, DOB, Gender, ContactNo
      from TblUsers
      where Gender = @UserGender)

When the command is executed successfully, the function is created and stored within the database. To locate it, expand the database in the Object Explorer, then navigate to Programmability and select Functions. Within the Functions folder, you should find the Inline Table-Valued Functions section. If you don't see it immediately, try refreshing the view by clicking the refresh icon or expanding the plus sign.

Inline tabled valued function

The implementation of the function is straightforward and easy to understand. We use the `CREATE FUNCTION` statement to define the function, where `UsersByGender` is the name of the function, indicating that it returns users based on gender.

The function requires a parameter `@UserGender` of type `nvarchar`, as gender is typically represented as a string. Therefore, we use the `nvarchar` data type for this parameter. Additionally, this function returns a table, denoted by the `RETURNS TABLE` clause.

How to call inline table valued function

To call this function, we use the syntax `SELECT * FROM function_name` and then pass whatever parameters are required. Since this function returns a table, we treat it as if it were a table itself. Therefore, we use the `SELECT * FROM` statement just like we would when selecting from a regular table.

In the case of an inline table-valued function, we issue a select statement against the function, treating it as if it were a table. So we use `SELECT * FROM function_name` to retrieve the results from the function.

select * from DemoDataBase.dbo.UsersByGender('Male')

If I want all the male users, I pass in 'male' as the parameter. Upon executing this query, only the male users are returned. Similarly, if I want female users, I pass 'female' as the parameter and execute the query to retrieve only the female users. This is the syntax used to call the function. Additionally, the WHERE clause can be used if needed.

select * from DemoDataBase.dbo.UsersByGender('Male') where name='Ammy'

As we know, a scalar function returns a single scalar data type, such as nvarchar, integer, or datetime. However, in this case, we want the function to return a list of employees based on gender, which necessitates returning a table. Therefore, we specify 'table' as the return type using the 'returns' keyword, followed by 'AS' and the return statement.

Look at the inline table-valued function. We are not using the Begin and END block.

If you recall, for scalar user-defined functions, we used a BEGIN and END block to enclose the function body. However, for an inline table-valued function, it's a compile-time error to include the BEGIN and END block. They are not allowed for an inline table-valued function.

CREATE FUNCTION [dbo].[UsersByGender](@UserGender nvarchar(10))
RETURNS TABLE
AS
RETURN (Select Id, Name,Email, DOB, Gender, ContactNo
      from TblUsers
      where Gender = @UserGender)

So immediately after the AS keyword, you specify the return type within brackets, followed by your select statement.

In this case, we want to retrieve columns such as Id, name, Email, DOB, gender, and ContactNo from the TblUsers table, filtered by the @UserGender parameter passed by the user.

We enclose the entire select statement in parentheses for clarity, although it's not strictly necessary. After completing the select statement, we return the output of its execution back to the calling application.

Comparison of inline table-valued functions with scalar, user-defined functions

So when someone calls your function, it returns whatever the select statement within it is selecting. Okay, there are three key points to remember when comparing inline table-valued functions with scalar user-defined functions:

1. Inline table-valued functions specify the table as the return type, while scalar functions specify scalar data types like Integer and nvarchar.

2. Scalar functions do not enclose the function body between begin and end blocks.

3. The structure of the table returned by an inline table-valued function is determined by the select statement within its body. The columns and their types are defined by the select list.

In this type of function, you don't explicitly define the structure of the table returned; instead, it's determined by the columns selected in the select statement. However, in the next session, we'll discuss multi-statement table-valued functions, where we have the flexibility to specify the table structure after the return keyword. We'll cover that in the next post.

Use of inline table-valued function

So where can we actually use this inline table-valued function. Inline-table value functions can be used to achieve the functionality of parametrized views

Now, what is a view, and what are parametrized views? Read Below Post

What is indexed or materialized view

But just keep in mind that inline table-valued functions are an exceptional replacement for parametrized views.

You can actually use it in Join as well. we know that the inline table-valued function returns a table. So the table returned by the table-valued function can be used in joins with other tables.

Table Script

CREATE TABLE [dbo].[TblUsers1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NOT NULL,
    [Password] [nvarchar](max) NULL,
    [Gender] [nvarchar](50) NOT NULL,
    [CreatedAt] [datetime2](7) NOT NULL,
    [DOB] [datetime2](7) NOT NULL,
    [ContactNo] [nvarchar](max) NULL,
 )

GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2, 'rajat Saxena', '[email protected]', 'BcpDvZOpQXnBkIihOB3y7A%3d%3d', 'Male', CAST('2019-11-17T16:56:35.8000000' AS DateTime2), CAST('1996-04-22T15:58:28.9210849' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (3, 'Pankaj Patel', '[email protected]', 'te9LBxguCUJFRbqSWozghg%3d%3d', 'Female', CAST('0001-01-01T00:00:00.0000000' AS DateTime2), CAST('2000-12-05T13:25:01.9770780' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (1003, 'Ammy', '[email protected]', 'BiYuRVMYd1Ue8iKoE%2bA2WA%3d%3d', 'Male', CAST('0001-01-01T00:00:00.0000000' AS DateTime2), CAST('1992-12-23T13:43:27.5816869' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (1004, 'Test 2', '[email protected]', 'BiYuRVMYd1Ue8iKoE%2bA2WA%3d%3d', 'Female', CAST('0001-01-01T00:00:00.0000000' AS DateTime2), CAST('1995-12-05T05:19:23.3650433' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (1005, 'Mark', '[email protected]', 'te9LBxguCUJFRbqSWozghg%3d%3d', 'Male', CAST('0001-01-01T00:00:00.0000000' AS DateTime2), CAST('2020-06-04T12:29:28.7768304' AS DateTime2), '0987654321')
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (1006, 'Priya', '[email protected]', 'te9LBxguCUJFRbqSWozghg%3d%3d', 'Female', CAST('0001-01-01T00:00:00.0000000' AS DateTime2), CAST('2019-12-05T05:22:11.8963884' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2004, 'Jonii', '[email protected]', 'BiYuRVMYd1Ue8iKoE%2bA2WA%3d%3d', 'Female', CAST('2019-11-25T11:29:06.9385410' AS DateTime2), CAST('2019-12-05T05:20:30.6045240' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2005, 'Jinny', '[email protected]', 'erxz7AefO88uggAc3kkx69Y5Sa1fOb4UBfCWJ8PNqWQ%3d', 'Female', CAST('2019-11-25T13:12:47.6690112' AS DateTime2), CAST('2019-12-05T05:20:34.2935856' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2006, 'Hr. Niels Henriksen', '[email protected]', 'Ih2xENnNQbBnjv07ia4QFQ%3d%3d', 'Male', CAST('2019-11-26T05:58:34.8949750' AS DateTime2), CAST('2019-12-05T05:20:37.5393081' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2007, 'Neeraj Singh', '[email protected]', 'SoIF2JssyCvePj9JyjNTXASEltv9VsXDIqxw4MQvkUw%3d', 'Male', CAST('2019-11-26T12:42:18.6778716' AS DateTime2), CAST('2019-12-05T05:20:40.9827126' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2008, 'Test 8', '[email protected]', 'te9LBxguCUJFRbqSWozghg%3d%3d', 'Female', CAST('2019-11-27T10:09:42.6969868' AS DateTime2), CAST('2019-11-27T10:09:42.6969869' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2009, 'Test 9', '[email protected]', 'te9LBxguCUJFRbqSWozghg%3d%3d', 'Female', CAST('2019-11-27T10:20:10.5816545' AS DateTime2), CAST('2019-11-27T10:20:10.5816545' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2010, 'Test 10', '[email protected]', 'te9LBxguCUJFRbqSWozghg%3d%3d', 'Female', CAST('2019-11-27T10:41:07.8215121' AS DateTime2), CAST('2019-11-27T10:41:07.8215122' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2011, 'Aankashi', '[email protected]', '0SM6YcqEHZq29W%2fFPtcLQw%3d%3d', 'Male', CAST('2019-11-30T05:36:52.1332523' AS DateTime2), CAST('2019-12-05T05:21:49.0948144' AS DateTime2), NULL)
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Password], [Gender], [CreatedAt], [DOB], [ContactNo]) VALUES (2012, 'Aakankshi Gupta', '[email protected]', 'kUEOh62X7XbOIEqBtV0FDQ%3d%3d', '0', CAST('2019-12-05T04:35:42.6189131' AS DateTime2), CAST('2019-12-05T04:35:42.6189131' AS DateTime2), NULL)
GO