时间戳

首页 > 解决方案 > SQLServer 查询中的用户定义函数不起作用

sql-server - SQLServer 查询中的用户定义函数不起作用

问题描述

我创建了一个 UDF 将文本拆分为 SQLServer AS 中的列

CREATE FUNCTION dbo.fn_Split50
(   
    @str varchar(max),
    @delim char(1), 
    @columnCnt int = 50
)
RETURNS TABLE 
AS
RETURN 
( SELECT * 
    FROM (SELECT 
            nn = (nn - 1) / @columnCnt + 1, 
            nnn = 'value' + cast(((nn - 1) % @columnCnt) + 1 as varchar(10)), 
            value
        FROM (SELECT 
            nn = ROW_NUMBER() over (order by (select null)),
            value
            FROM string_split(@str, @delim) aa
            ) aa
        where nn > 0
    ) bb
    PIVOT  
    (  
    max(value) 
    FOR nnn IN (    
        value1, value2, value3, value4, value5, value6, value7, value8, value9, value10,
        value11, value12, value13, value14, value15, value16, value17, value18, value19, value20,
        value21, value22, value23, value24, value25, value26, value27, value28, value29, value30,
        value31, value32, value33, value34, value35, value36, value37, value38, value39, value40,
        value41, value42, value43, value44, value45, value46, value47, value48, value49, value50        
     )  
    ) AS PivotTable 
)

当我用硬编码字符串测试它时，它工作正常

SELECT * FROM  dbo.fn_split50('Cate,Robert Anderson, Mary Jame williams' ,   ',', DEFAULT)

但是，当我在查询中使用它时会出错

CREATE TABLE #Data (Cols varchar(120))
INSERT INTO #Data VALUES
('James Ray,Mark will'),
('Cate,Robert Anderson, Maryy Jame williams'),
('Johnson Author, Carson')

 SELECT 
  dbo.fn_Split50(Cols, ',', DEFAULT)
 FROM  #Data

错误信息

Msg 4121, Level 16, State 1, Line 14
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_Split50", or the name is ambiguous.

有什么我做错了吗？

标签： sql-servertsql

解决方案

表值函数返回一个表。因此，就像您在第一种情况下所做的那样，您必须将函数调用放在FROM. 第二种情况失败，因为表不是要放在 之后的列/表达式SELECT。

您可能这样做是因为您的 #Data 表提供了函数的参数。这样做的方法是CROSS APPLY：

CREATE TABLE #Data (Cols varchar(120))
INSERT INTO #Data VALUES
('James Ray,Mark will'),
('Cate,Robert Anderson, Maryy Jame williams'),
('Johnson Author, Carson')

 SELECT f.*
 FROM  #Data 
 cross apply dbo.fn_Split50(#Data.Cols, ',', DEFAULT) as f

推荐阅读