A table valued function is a function that returns a table instead of a single value.  In this post, we will go over the Table Valued Functions in more detail than we did in the general topic covering User Defined Functions.  There are many uses for table valued functions so we will go over a couple of examples to ensure that you will be successful when you begin to create your own.

The first thing you need to understand is how you define a function as a Table Valued Function.  This is done by defining a table variable as the type of value returned.  The syntax for declaring the output table variable is similar to how you would declare a table variable in a stored procedure with the exception that instead of using the DECLARE keyword, you use RETURNS. The next thing, like every other function, is that you must have

In the below example we do a simple insert of two values into the table and returns.

create function dbo.fn_dosomething()
returns @A_Table TABLE
     (ident    int identity primary key clustered
     ,theValue nvarchar(50)
     )
as
begin

insert into @A_Table(theValue) values('Hello')
insert into @A_Table(theValue) values('Good Bye')

return
end
go

The below example is made to take a string containing a name and divide it into the first and last name.  The below function will take a single input parameter and divide it into first and last name.  This would be useful if, due to poor design decisions, the user table contained a single field for name instead of first and last names.  Since the name field is a single field, you have no control over what format people entered their data in.  They could have done "Last Name, First Name", "First Name Last Name" or simply "First Name".  This function implements business logic to determine how the various values should be parsed.

The Function:

create function dbo.fn_SplitName(@Name varchar(100))
returns @Names table (FirstName varchar(100), LastName varchar(100))
as
begin
if datalength(@Name)>0
begin
     if charindex(',',@name)>0           -- Supports "Lastname, Firstname" Format
          insert into @Names (LastName, FirstName)
          select left(@Name, charindex(',',@Name)-1), 
                      ltrim(rtrim(right(@Name, charindex(',', reverse(@Name))-1)))
     else if charindex(' ', @Name)>0     -- Supports "Firstname LastName" Format
          insert into @Names (LastName, FirstName)
          select ltrim(rtrim(right(@Name, charindex(' ', reverse(@Name))-1))), 
                      left(@Name, charindex(' ',@Name)-1)
     else                                -- If no match above, return as first name.
          insert into @Names (FirstName)
          values(@Name)

end
return
end
go

This function searches for certain characters within the passed variable.  It is not intended to be a useful function for parsing ALL formats of names, those have to be defined by your organization or needs.  If you have the need for a specific User Defined Function, please post it in our SQL Help Forums and someone will help you out as soon as possible.

Table Valued Functions are useful in the place of views where you need to define parameters for what data is selected.