---

Search posts under SQL Server:

---

Create Multi-statement Table-valued function in SQL Server

How to create Multi-statement Table-valued function in SQL Server?

Multi-statement Table-valued Function is used in case we want to create a more complex function that may use conditions, loops etc. To create a simple Table-valued Function, click here.

To create Multi-statement Table-valued Function, right click Table-valued Functions from Programmability > Functions

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

ALTER FUNCTION [dbo].[GetAdultRecords]
(
       @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, ac.Salary
                     FROM PersonalDetails pd JOIN Accounts ac ON
                     pd.PersonalDetailsId = ac.PersonalDetailsId
                     WHERE pd.Age < 18
              END
       ELSE
              BEGIN
                     INSERT INTO @myRecords
                     SELECT pd.FirstName + ' ' + pd.LastName fullName, ac.Salary
                     FROM PersonalDetails pd JOIN Accounts ac ON
                     pd.PersonalDetailsId = ac.PersonalDetailsId
                     WHERE pd.Age >= 18\
              END
       RETURN ;
END

In the above case, we are getting the input parameter of GetAdultRecords 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 PersonalDetails and Salary from Accounts 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 GetAdultRecords('major')
SELECT * FROM GetAdultRecords('minor')

This will give desired result in the result window.