将表作为参数传递到 sql server UDF

2022-01-31 00:00:00 sql user-defined-functions parameters sql-server-2005 sql-server

我想将一个表作为参数传递给一个缩放器 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 确保没有空值并确保没有重复项.我期待的是:

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 是正确的，而且功能是 long 逾期

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

相关文章