How to create Inline table-valued functions in SQL server

In the post, we learn about creating and calling inline table-valued functions. Where do we use inline table-valued functions? now Before continuing with the session, I strongly recommend reading this post.

• Scalar use-defined functions-Basic Concept

In the post, we have discussed how to create and call scalar user-defined functions.

In this part, we learn about the inline table value functions. We know that a scalar function returns a scalar value as a single value, whereas an inline table-valued function returns a table.

Now let’s look at an example of creating an inline table-valued function.

if you look at this, I’ve got a TblUsers table we just got I’d name, Email, Password, Gender, DOB, and ContactNo columns. Now I want you to write a function that can give me the output that you can see in the below image.

The function should return users by gender. For example, if I passed in function gender as male, I want only the male users to be returned and along the same lines If I pass the gender as female and I only want the female users to 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)

 

so let’s execute this. when I execute command completed successfully.so where did this function get created? once I expand the database, go to programmability and then functions there,You should see inline table-valued functions. So that gets stored in this table-valued functions folder.If you can’t see that, just refresh it and expand the plus sign.

if you look at the implementation of the function here, it’s very simple to understand.So we use the create function statements so create function function_name and UsersByGender is the name of the function because we are going to return users by gender.

And if you look at function, obviously this function needs a parameter @UserGender of type nvarchar.since gender is a string we use nvarchar data type, and then this function returns table.  so this is the parameter that we are passing into this function and this function returns table.

how to call inline table valued function

Now, let us see how to call this function, so obviously, to call this function, you know, we use the syntax select * from the function_name and then pass whatever parameters you have to pass to that function.

Now, this function returns the table, so treat it as it is a table. now if we want to select from a table, we will select * from that table_name.

Similarly, this is an inline table-valued function just issue a select statement against the inline table value function as if you would issue a statement against a table. So select * from that function_name.

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

I want all the male users so I am passing in male and when we execute this query we only get the male users back and along the same lines.

If I want female users I just pass female and execute the query so I only get the female users.that’s the syntax that we use to execute. I mean to call the function.Now if you want, you can use the where clause

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

As we know scalar function returns a scalar data type, for example, maybe nvarchar or integer or daytime, a scalar value. But we want this function to return a list of employees by gender.

so obviously we want this function to return a table. So we are specifying table as the return type from this function using the returns Keywood and then we are using AS keyword and return.

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

If you remember for the scalar user-defined function we had Begin and END block, the function body was enclosed within that block, but for an inline table-valued function, it’s a compile-time error to have the Begin and END block. You cannot have it for an inline table value 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 and within brackets, you specify your select statement.

so what do we want here? We want Id, name, Email, DOB, gender, and ContactNo columns, so specify them in your select list from TblUsers table where gender equals @UserGender parameter, whatever the user is going to pass in.

so we are using that parameter there and we are inclosing that entire select statement in parentheses.

I mean even if you don’t indicate that we don’t get an error but it is more readable now after whatever is present in the parentheses is completed return the output of the execution result of the select statement back to the calling application.

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

So when somebody calls your function, return whatever your select statement is selecting here. OK, so if you notice, there are three points to keep in mind when we compare inline table-valued functions with scalar, user-defined functions.

In inline table-valued functions, we specify the table as the return type, whereas for scalar functions we specify any of the scalar data types like Integer and nvarchar.For scalar functions, the function body is not enclosed between begin and end block.

The structure of the table, it is very important. Now, look at this. You are telling your function it’s going to return a table. But what are the columns that are going to be present in that table? You know, basically the structure of the table.

We are not defining that here.so the structure of the table will be determined by the select statement that you have.

So obviously the table that this function is going to return is going to have five columns because your select query has five columns within it Select list.

so the structure of the table that gets returned is determined by the select statement within the function body,  but later in the next session, we’ll be talking about multi-statement table-valued function there we have the luxury of specifying the structure of the table after the return key.we will talk about 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', 'rajat.nirmal@gmail.com', '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', 'Pankaj@gmail.com', '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', 'pankajabc@gmail.com', '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', 'pankajabcd@gmail.com', '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', 'test@test.com', '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', 'test1@test.com', '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', 'pankajws@gmail.com', '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', 'pankajadequate@gmail.com', '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', 'contact@adequateinfosoft.com', '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', 'neirajsingh101@gmail.com', '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', 'test3@test.com', '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', 'test10@gmail.com', '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', 'test02@gmail.com', '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', 'alokpatel@gmail.com', '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', 'aakankshi@gmail.com', 'kUEOh62X7XbOIEqBtV0FDQ%3d%3d', '0', CAST('2019-12-05T04:35:42.6189131' AS DateTime2), CAST('2019-12-05T04:35:42.6189131' AS DateTime2), NULL)
GO

The post How to create Inline table-valued functions in SQL server appeared first on Software Development | Programming Tutorials.