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