Introduction:

In this article, we are going to explain what is Multi-statement Table-Valued User-Defined Function in SQL or how to create Multi-statement Table-Valued User-Defined Function in SQL or how to use Multi-statement Table-Valued User-Defined Function in SQL server with an example.

Description:

A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one T-SQL statement. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, we can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return recordsets.

Creating and Implementing Multi-statement Table-Valued User-Defined Function

CREATE FUNCTION Fn_Getcustomer(@id int)
RETURNS @TRACKCUSTOMER TABLE (
   Id int NOT NULL,
   NAME VARCHAR(120) NULL,
   ADDRESS1 VARCHAR(250) NULL
) 
AS
BEGIN
   INSERT INTO @TRACKCUSTOMER (Id, NAME, ADDRESS1)
   SELECT ID, NAME, ADDRESS1 FROM CUSTOMER WHERE ID= @id; 
   RETURN;
END;

Example: In above example, we are creating a Multi-Statement Table-Valued function GetCustomerByName which accepts the name as the input parameter and returns the customer id and name of all the customer belonging to the input name. If for the input name there are no customer then this UDF will return a record with no id column value and name as ‘No customer Found’. 

 We can use the below statements to get all the customer in the given input name:

SELECT * FROM dbo.GetCustomerByName('RAJEEV')
SELECT * FROM dbo.GetCustomerByName('ANIL')

 OutPut:

I Hope it will help you after reading it.

you might also want to see Scalar User-Defined Function and Inline Table-Valued