将表作为参数传递到SQL Server UDF中 [英] Pass table as parameter into sql server UDF

问题描述

我想将表格作为参数传递给定标器UDF.

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)

这可能吗?

编辑

我不想传递表名，我想传递数据表(我想作为参考)

I don't want to pass a table name, I'd like to pass the table of data (as a reference I presume)

编辑

我希望我的Scaler UDF基本上取一个值表并返回行的CSV列表.

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"

会返回

"My First Value, My Second Value,... My nth Value"

不过，我想对表进行一些过滤，即IE确保没有null并确保没有重复项.我期待着以下方面的事情:

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)

推荐答案

不幸的是，SQL Server 2005中没有简单的方法.尽管Lukasz的答案对SQL Server 2008是正确的，但功能却很长过期

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

任何解决方案都将涉及临时表，或传入xml/CSV并在UDF中进行解析.示例:更改为xml，在udf中解析

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...

为什么不以字符串形式传递查询并使用带有输出参数的存储过程

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

注意:这是未经测试的代码，您需要考虑SQL注入等.但是，它也满足您的单列"要求，应该会帮助您

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

这篇关于将表作为参数传递到SQL Server UDF中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！