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)
    declare @return varchar(500)
    set @return = stuff((select @Separator + value from @table for xml path('')),1,1,'')
    return @return

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, '-')

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Next Post »