CODEWITHSUNDEEP
sqlsql-serverfunctiont-sql
Jay Desai
Jay Desai

Reputation: 861

Getting error while running function using T-SQL

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');

Upvotes: 1

Views: 738

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

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;

Upvotes: 2

Alan Burstein
Alan Burstein

Reputation: 7918

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

Upvotes: 1

Related Questions