Pass Table as Parameter into SQL Server Udf

Pass table as parameter into sql server UDF

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

Table variable in User Defined Function SQL Server and performance of UDF

You can't really do what you want.

First, you cannot pass tables as parameters into functions. As the documentation explains:

Parameters can take the place only of constants; they cannot be used instead of table names, column names, or the names of other database objects.

In theory, you could use dynamic SQL to construct a query and run it. Unfortunately, the only use of execute is for extended functions. This is a little buried in the documentation, but it is there:

User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

That really doesn't leave any good options. You might try to think of another approach that doesn't require storing related data in multiple different tables. Or perhaps you can use a stored procedure instead of a UDF.

There are some very arcane work-arounds, which involve using an extended stored procedure or CLR to execute a batch command which in turn passes a query into the database. That is really complicated, prone to error, and I've never actually seen it used in production code.

SQL - Pass Table to a UDF in a Select

Subqueries can't be used like that to return table variables in the SELECT portion of the query. They can only return a single value. As in:

set @myParam = (select myvalue from table where primarykey = 1)

or

set @myParam = (select top 1 myvalue from table)

But, considering that your subqueries do not have any relation to the rows being selected, you can do something like this.

declare @t1 table (DateTime c1, float c2)
insert @t1
SELECT Date, Revenue 
FROM #Table 

declare @t2 table (DateTime c1, int c2) -- not sure on data type of 'Orders'
insert @t2
SELECT Date, Orders FROM #Table


SELECT T.unit               
     , T.line               
     , dbo.fn_myUDF(@t1) as UDFRevenueResult               
     , dbo.fn_myUDF(@t2) as UDFOrderResult 
FROM #Table T 
WHERE T.Date = @ReportDate 

Pass table as parameter to SQLCLR TV-UDF

Turns out that there's a fixed list of valid inputs on a SQLCLR function, determined by the available mapping between .NET datatypes and SQL datatypes

SQL Datatype "table" is explicitly called out as having no mapping through the CLR.

Ergo, it's not possible to pass table-valued data INTO a table-valued CLR function as method parameters.

Alternatives

It does seem possible to get tabular data in via select ... for xml contortions to feed into a SqlXml parameter.

I have successfully used SqlConnection conn = new SqlConnection("context connection = true"); in the .NET code to let the TVF query the DB for the tabular data it needs.

Pass a table variable as a parameter to a table-valued function

I'm assuming that udf_SplitString takes in a single value and returns a table. If that's the case then I think you want:

SELECT ss.*
FROM Employees e
CROSS APPLY udf_SplitString(e.EmpName) AS ss

Create T-SQL Function with table parameter

Beginning from SQL Server 2008 you can use table valued parameters:

CREATE TYPE [dbo].[TableType] AS TABLE(
[ID] [INT] NULL
)
GO

CREATE FUNCTION fnTest
    (
      @t [dbo].[TABLETYPE] READONLY
    )
RETURNS INT
AS
    BEGIN

        RETURN (SELECT TOP 1 ID FROM @t ORDER BY id DESC)

    END
GO

DECLARE @t [dbo].[TABLETYPE]
INSERT  INTO @t
VALUES  ( 1 ),
        ( 2 )

SELECT  dbo.fnTest(@t) AS ID

Output:

ID
2

---

Related Topics