I'd like to pass a table as a parameter into a scaler UDF.
I'd also prefer to restrict the parameter to tables with only one column. (optional)
Is this possible?
I don't want to pass a table name, I'd like to pass the table of data (as a reference I presume)
I would want my Scaler UDF to basically take a table of values and return a CSV list of the rows.
col1 "My First Value" "My Second Value" ... "My nth Value"
"My First Value, My Second Value,... My nth Value"
I'd like to do some filtering on the table though, IE ensuring that there are no nulls and to ensure there are no duplicates. I was expecting something along the lines of:
SELECT dbo.MyFunction(SELECT DISTINCT myDate FROM myTable WHERE myDate IS NOT NULL)
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 xml SELECT @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) ) AS SET NOCOUNT ON CREATE TABLE #foo (bar varchar(max)) INSERT #foo EXEC (@MyQuery) SELECT @CSVOut = SUBSTRING(buzz, 2, 2000000000) FROM ( SELECT bar -- maybe CAST(bar AS varchar(max))?? FROM #foo FOR XML PATH (',') ) fizz(buzz) GO