Pass table as parameter into sql server UDF
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
select dbo.fn_get_string_with_delimeter(@tab, ',')
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 the function 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 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 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
Cutting to the bottom line, you want a query like SELECT x FROM y to be passed into a function that returns the values as a comma separated string.
As has already been explained you can do this by creating a table type and passing a UDT into the function, but this needs a multi-line statement.
You can pass XML around without declaring a typed table, but this seems to need a xml variable which is still a multi-line statement i.e.
DECLARE @MyXML XML = (SELECT x FROM y FOR XML RAW);
SELECT Dbo.CreateCSV(@MyXml);
The "FOR XML RAW" makes the SQL give you it's result set as some xml.
But you can bypass the variable using Cast(... AS XML). Then it's just a matter of some XQuery and a little concatenation trick:
CREATE FUNCTION CreateCSV (@MyXML XML)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @listStr VARCHAR(MAX);
SELECT
@listStr =
COALESCE(@listStr+',' ,'') +
c.value('@Value[1]','nvarchar(max)')
FROM @myxml.nodes('/row') as T(c)
RETURN @listStr
END
GO
-- And you call it like this:
SELECT Dbo.CreateCSV(CAST(( SELECT x FROM y FOR XML RAW) AS XML));
-- Or a working example
SELECT Dbo.CreateCSV(CAST((
SELECT DISTINCT number AS Value
FROM master..spt_values
WHERE type = 'P'
AND number <= 20
FOR XML RAW) AS XML));
As long as you use FOR XML RAW all you need do is alias the column you want as Value, as this is hard coded in the function.