Create Function returns multiple values sql

We can create a Function which can returns multiple values. As the definition says, the Scalar valued function returns only single value.

But on using a Data type TABLE, we can get multiple values.

Example is shown below:

I have a Table called family.

Now let me create a function to call all the records where I am going pass FamilyID as a parameter.

Create FUNCTION dbo.Fn_Family (@ID AS VARCHAR(50))

RETURNS TABLE

RETURN

SELECT FirstName, LastName FROM dbo.Family WHERE FamilyID = @ID;

The call this function, like:

Select * from dbo.Fn_Family(1)

Where (1) is the parameter.

MSBI