Never done it but I think the CLR type in the function will have to be a System.Data.DataTable. Passing XML is another option.
On Wed, Jun 29, 2011 at 12:59 PM, Shane Fitch <shane.fitch...@gmail.com> wrote: > Hi Greg, > We use a user defined table type, something like: > ALTER FUNCTION [dbo].[MyFunction] > ( > @MyParameter [dbo].[MyUserDefinedTableType] READONLY > ) > On Wed, Jun 29, 2011 at 12:32 PM, Greg Keogh <g...@mira.net> wrote: >> >> Folks, has anyone written a SQL Server user-defined function (not a proc) >> that takes a TVP (Table-Valued Parameter)? >> >> >> >> I’m writing a function that needs to take a variable number of strings as >> arguments. It’s unlikely I can define a SQL function that takes the >> equivalent of “params string[] value”, so it seems logical to pass a table >> instead with all the strings in a column. The docs encourage me by saying >> this: >> >> >> >> Table-valued parameters are a new parameter type in SQL Server 2008. >> Table-valued parameters are declared by using user-defined table types. You >> can use table-valued parameters to send multiple rows of data to a >> Transact-SQL statement or a routine, such as a stored procedure or function, >> without creating a temporary table or many parameters. >> >> >> >> However, the coding syntax for the CLR code and CREATE FUNCTION have me >> flummoxed. Has anyone done this, or know the syntax? I’m still searching the >> web without success. >> >> >> >> Greg >
