Using a user defined function in SQL returning a table based on 2 parameters

Question

I've defined a UDF that splits a certain string into multiple substrings based on a type I provide.

ALTER FUNCTION Split (@MainString varchar(100), @Type char)
RETURNS @PartsTable TABLE
(
    C1      varchar(10) NULL,
    C2      varchar(10) NULL,
    C3      varchar(50) NULL,
    C4      varchar(10) NULL,
    C5      varchar(50) NULL
)
AS
BEGIN       
    declare @pC1    as varchar(10)  
    declare @pC2    as varchar(10)
    declare @pC3    as varchar(50)
    declare @pC4    as varchar(10)
    declare @pC5    as varchar(50)

    -- cut string into pieces   
    /*
    do some work
    */

    -- fill return table 
    INSERT @PartsTable (C1, C2, C3, C4, C5)
           select @pC1, @pC2, @pC3, @pC4, @pC5
    RETURN
END

It provides me with the correct resultset:

C1      C2     C3     C4     C5
--------------------------------------
M.C.    NULL   Test   NULL   NULL

(1 row(s) affected)

When I call it like this:

SELECT *
FROM   dbo.[Split]('Test M.C.', 'X')

How can I incorporate this in a normal SQL query, I was trying something like follows for a table;

SELECT *
FROM   MyTable X, dbo.Split(X.ColumnString, X.Type)

But that doesn't work. It provides me with the following error:

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

Does anyone know how I should call this UDF?

Thanks in advance.

StuartLC · Accepted Answer · 2014-01-15 16:35:21Z

2

Use CROSS APPLY in this situation, e.g.

select *
    from MyTable X cross apply dbo.Split(X.ColumnString, X.Type);

SqlFiddle here

edited Jan 15, 2014 at 16:35

answered Jan 15, 2014 at 16:28

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Obelix Over a year ago

Thanks a lot! Never heard of the cross apply method. Superstuff :)

fantome195 · Accepted Answer · 2016-02-03 01:06:47Z

1

this happend because function returns is a table so you should treat function as table

select * from  [dbo].[Split] ('karim pentest')

[dbo].[Split] : is the name of my function

answered Feb 3, 2016 at 1:06

fantome195

312 bronze badges

Collectives™ on Stack Overflow

Using a user defined function in SQL returning a table based on 2 parameters

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related