User Defined Function in SQLServer query not working

Question

I created a UDF to split text into columns in 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 
)

When I tested it with hard coded string it works fine

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

However when I use it in a query it gives an error

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

Error message

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.

Is there anything i am doing wrong?

You function returns table, so it cannot be used in a select statement as a column. In other words: you can select from table, but you cannot select the table. — Michał Turczyn
– Michał Turczyn, Commented Jan 30, 2020 at 9:25

George Menoutis · Accepted Answer · 2020-01-30 09:25:00Z

A table-valued function returns a table. As such, just like you did in the first case where it worked, you have to put the function call after FROM. The second case fails as a table is not a column/expression to be put after SELECT.

You probably did this because your #Data table provides the function's arguments. The way to do this is 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

Collectives™ on Stack Overflow

User Defined Function in SQLServer query not working

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related