Can Function Return a Table in SQL Server 2008 or Can SQL Function return a table as Parameter

Here I will explain how SQL Server function will return table as parameter in SQL Server 2008 or can SQLfunction return a table as parameter in SQL Server 2008.    

Description:

In previous posts I explained Show time in Minute Ago, hour ago, day ago in SQL Server, SQL Server difference between view and stored procedure, SQL Server Get Date or Time from datetime column SQL Server split function example to split comma separated string, SQL Server update statement with inner joins, interview questions in asp.net, sql server, c# and many articles relating to SQL Server. Now I will explain how SQL Server function will return table in SQL Server 2008.

Whether functions will return table as parameter or not?

For this question answer will be Yes functions will return table as parameter by using table valued parameters.  

Generally SQL Server functions will return only one parameter value if we want to return multiple values from function then we need to send multiple values in table format by using table valued functions.

Sample function which return table as parameter

CREATE FUNCTION testmultiplevalues ( @UserId INT ) returns table as return ( Select * from UserInformation WHERE UserId=@userId )

We can call this function in our query like as shown below

SELECT * FROM dbo.testmultiplevalues(14)

If we run above query we will get output like as shown below

Demo