---

Search posts under SQL Server:

---

Create a Table-valued function in SQL Server

How to create a Table-valued function in SQL Srever?

As explained above, a table-valued function returns a tabular data. To create this, we can right click the table-valued Functions option under Programmability > Functions and choose “New Inline Table-valued Function….”.

This will create a new query window with table-valued user defined function template. Replace the placeholders for name, data types etc. as per need.

CRETAE FUNCTION [dbo].[GetAdultPeople]
(
       @age int
)
RETURNS TABLE
AS
RETURN
(
       -- Add the SELECT statement with parameter references here
       SELECT * FROM PersonalDetails WHERE Age >= @age
)

In above case, the function name is GetAdultPeople with @age parameter. It is returning all details from PersonalDetails table whose age is greater than @age parameter value.

To call this function, call as if this is a physical table (as this returns a tabular structure data).

SELECT * FROM dbo.GetAdultPeople(18)

Important

We can also ignore “dbo” (DataBase Owner) word however it is suggested to use that as prefixed with the function name. If we have created this function by logging in with another user, we should prefix the function name with that username and call it.

Also, conditional (IF ELSE) statement is not permitted in “New Inline Table-valued Function….”, if we want to perform more complex operations in functions in SQL Server, we can use “Multi-statement Table-valued function”.