Getting error while running function using T-SQL

Question

I have created small table value function using T-SQL which takes one input parameter phone number as and returns area code from it. Function compiles successfully but when I run it I am getting error:

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

Refer to my code:

create or alter function dbo.getareacode1 (@phoneno as nvarchar(20))
returns table
with schemabinding
as
    return
       (select top 1 
            @phoneno as PhoneNumber, value as AreaCode 
        from 
            string_split(@phoneno,'-')
       );


select dbo.getareacode1( N'323-234-2111');

David Browne - Microsoft · Accepted Answer · 2020-10-31 22:47:04Z

2

First off, the order of the rows returned from STRING_SPLIT is not guaranteed, so this function broken to begin with.

But the error is caused by trying to invoke a Table-Valued Function where a scalar is expected.

A TVF cannot be treated like a scalar function. So

create or alter function dbo.getareacode1 (@phoneno as nvarchar(20))
returns table
with schemabinding
as
return
(
select top 1 @phoneno as PhoneNumber, value as AreaCode 
from string_split(@phoneno,'-')
);
go
select * from dbo.getareacode1( N'323-234-2111');

And if you want to call it from another query use CROSS APPLY, eg

with phoneNumbers as
(
    select N'323-234-2111' phoneno
    from sys.objects 
)
select ac.*
from phoneNumbers p
cross apply dbo.getareacode1(p.phoneno) ac;

edited Oct 31, 2020 at 22:47

answered Aug 16, 2017 at 0:21

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

Comments

Community · Accepted Answer · 2018-11-13 22:53:48Z

On the flip-side of what David said: for your function to work like you were expecting you will need a scalar user-defined function (udf). This function:

-- Create function
create or alter function dbo.getareacode1 (@phoneno as nvarchar(20))
returns nvarchar(20) with schemabinding as
begin
  return
  (
    select top (1) AreaCode = ss.[value]
    from string_split(@phoneno,'-') ss
  );
  end
GO

... will allow this query to work:

select dbo.getareacode1( N'323-234-2111');

All that said, I strongly advise against using scalar udfs. I'll include a a couple good links that explain why at the bottom of this post. Leaving your function as-is and using APPLY, as David demonstrated, is the way to go. Also, string_split is not required here. If phone numbers are always coming in this format: NNN-NNN-NNNN, you could just use SUBSTRING:

SUBSTRING(N'323-234-2111', 1, 3).

For countries with varible-length area codes in the format (area code(2 or more digits))-NNN-NNNN you could do this:

declare @phoneno nvarchar(30) = N'39-234-2111'; -- Using a variable for brevity
select substring(@phoneno,1, charindex('-',@phoneno)-1);

If you, for whatever reason, really need a function, then this is how I'd write it:

-- using the variable-length requirement as an example
create or alter function dbo.getareacode1_itvf(@phoneno as nvarchar(20))
returns table with schemabinding as
return (select areacode = substring(@phoneno,1, charindex('-',@phoneno)-1));
go

Great articles about Scalar UDFs and why iTVFs are better:

How to Make Scalar UDFs Run Faster -- Jeff Moden

Scalar functions, inlining, and performance -- Adam Machanic

TSQL Scalar functions are evil – Andy Irving -- Stackoerflow post

Collectives™ on Stack Overflow

Getting error while running function using T-SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related