Passing table to Function SQL Server

In SQL Server, there is no direct way of passing table to a function.

In this article, I am sharing how you can do it with the help of user-defined Type as table and Table-valued parameter which were introduced in SQL Server 2008

In SQL Server 2008, we can create user-defined Type as table that represents the definition of a table structure. We can then use this type to declare table-valued parameters for functions or to declare table variables that you want to use in a function.

Let's take an example where we will pass a table with one column and a separator to the function and it will return all table column values with the passed separator as a single value.

Step 1: Create a Type as Table with name TableType that will accept a table having one varchar column

create type TableType
as table ([value] [varchar](100) null)

Step 2: Create a function that will accept above declared TableType as Table-Valued Parameter and String Value as Separator

create function dbo.fn_get_string_with_delimeter (
    @table TableType readonly, @Separator varchar(5)
)
returns varchar(500)
as
begin
    declare @return varchar(500)
    set @return = stuff((select @Separator + value from @table for xml path('')),1,1,'')
    return @return
end

Step 3: Pass table with one varchar column to the user-defined type TableType and '-' as separator in the function

declare @tab TableType
insert into @tab
select 'Amit' union all
select 'Sandeep' union all
select 'Abhay' union all
select 'Ritesh'
select dbo.fn_get_string_with_delimeter(@tab, '-')

OUTPUT