Create Table-valued function in SQL Srever

Updated on August 30, 2015     Kisan Patel

This tutorial will show you 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.

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

In above case, the function name is GetAdultPeople with @age parameter. It is returning all details from Employees 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)

Note: 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”.