Create Multi-statement Table-valued function in SQL Server?

Updated on August 30, 2015     Kisan Patel

This tutorial will show you how to create Multi-statement Table-valued function in SQL Server?

To create multi-statement table-valued function, right click Table-valued Functions from Programmability > Functions as shown in below screenshot:

This will open up a template for Multi-statement Table-valued Function, replace different placeholders for name, data type etc.

	Alter FUNCTION [dbo].[GetAdultPeople1]
	(
		@ageType varchar(10)
	)
	RETURNS @myRecords TABLE
	(
		fullName varchar(50) not null,
		salary money
	)
	AS
	BEGIN
	-- Fill the table variable with the rows for your result set
	IF (@ageType = 'minor')
		BEGIN
		INSERT INTO @myRecords
		SELECT pd.FirstName + ' ' + pd.LastName fullName, pd.Salary 
		FROM Employees pd WHERE pd.Age < 18 		END 	ELSE 		BEGIN 		INSERT INTO @myRecords 		SELECT pd.FirstName + ' ' + pd.LastName fullName, pd.Salary  		FROM Employees pd WHERE pd.Age > 18
		END
	RETURN ;
	END

In the above case, we are getting the input parameter of GetAdultPeople1 function as @ageType. The tabular data (@myRecords) this function returns contains fullName and salary column (virtual table created in this function).

In the following lines of code, based on @ageType value we are getting combination of FirstName and LastName as FullName from Employees and Salary from Employees table and inserting into virtual table @myRecords and the same is being returned.

To call this function, use this as a table in the SELECT statement.

	SELECT * FROM [GetAdultPeople1]('major')
	SELECT * FROM [GetAdultPeople1]('minor')

This will give desired result in the result window.