Pass table as parameter into sql server UDF

You can, however no any table. From documentation:

For Transact-SQL functions, all datatypes, including CLR user-definedtypes and user-defined table types,are allowed except the timestamp datatype.

You can use user-defined table types.

Example of user-defined table type:

CREATE TYPE TableType AS TABLE (LocationName VARCHAR(50))GO DECLARE @myTable TableTypeINSERT INTO @myTable(LocationName) VALUES('aaa')SELECT * FROM @myTable

So what you can do is to define your table type, for example TableType and define the function which takes the parameter of this type. An example function:

CREATE FUNCTION Example( @TableName TableType READONLY)RETURNS VARCHAR(50)ASBEGIN    DECLARE @name VARCHAR(50)    SELECT TOP 1 @name = LocationName FROM @TableName    RETURN @nameEND

The parameter has to be READONLY. And example usage:

DECLARE @myTable TableTypeINSERT INTO @myTable(LocationName) VALUES('aaa')SELECT * FROM @myTableSELECT dbo.Example(@myTable)

Depending on what you want achieve you can modify this code.

EDIT:If you have a data in a table you may create a variable:

DECLARE @myTable TableType

And take data from your table to the variable

INSERT INTO @myTable(field_name)SELECT field_name_2 FROM my_other_table

Unfortunately, there is no simple way in SQL Server 2005. Lukasz' answer is correct for SQL Server 2008 though and the feature is long overdue

Any solution would involve temp tables, or passing in xml/CSV and parsing in the UDF. Example: change to xml, parse in udf

DECLARE @psuedotable xmlSELECT    @psuedotable = ...FROM    ...FOR XML ...SELECT ... dbo.MyUDF (@psuedotable)

What do you want to do in the bigger picture though? There may be another way to do this...

Edit: Why not pass in the query as a string and use a stored proc with output parameter

Note: this is an untested bit of code, and you'd need to think about SQL injection etc. However, it also satisfies your "one column" requirement and should help you along

CREATE PROC dbo.ToCSV (    @MyQuery varchar(2000),    @CSVOut varchar(max))ASSET NOCOUNT ONCREATE TABLE #foo (bar varchar(max))INSERT #fooEXEC (@MyQuery)SELECT    @CSVOut = SUBSTRING(buzz, 2, 2000000000)FROM    (    SELECT         bar -- maybe CAST(bar AS varchar(max))??    FROM         #foo    FOR XML PATH (',')    ) fizz(buzz)GO

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

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

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

select dbo.fn_get_string_with_delimeter(@tab, ',')