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?
EDIT
I don't want to pass a table name, I'd like to pass the table of data (as a reference I presume)
EDIT
I would want my Scaler UDF to basically take a table of values and return a CSV list of the rows.
IE
col1
"My First Value"
"My Second Value"
...
"My nth Value"
would return
"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)
You can, however no any table. From documentation:
For Transact-SQL functions, all data types, including CLR user-defined types and user-defined table types, are allowed except the timestamp data type.
You can use user-defined table types.
Example of user-defined table type:
CREATE TYPE TableType
AS TABLE (LocationName VARCHAR(50))
GO
DECLARE @myTable TableType
INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT * FROM @myTable
So what you can do is to define your table type, for example TableType
and define funcion which takes the parameter of this type.An example function:
CREATE FUNCTION Example( @TableName TableType READONLY)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @name VARCHAR(50)
SELECT TOP 1 @name = LocationName FROM @TableName
RETURN @name
END
The parameter has to be READONLY. And example usage:
DECLARE @myTable TableType
INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT * FROM @myTable
SELECT dbo.Example(@myTable)
Depending of 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