Getting a null value not expected for user defined function

Question

I'm stuck trying to create a function to display customer names based on area code.

Function is created, but I'm getting null results for known area codes where I should be getting lots.

I'm a clueless newbie, and that may be a factor.

alter function ufnGetCustomerByAreaCode(@AreaCode nvarchar(3))
returns nvarchar(100)
as
begin
declare @Customer as nvarchar(100);
set @Customer =
    (
        select c.FirstName + ' ' + c.LastName
        from SalesLT.Address as a
        join SalesLT.CustomerAddress as ca
        on a.AddressID = ca.AddressID
        join SalesLT.Customer as c
        on ca.CustomerID = c.CustomerID
        where left(a.PostalCode,3) = @AreaCode
    )
    return @Customer
    end

Using the command

select dbo.ufnGetCustomerByAreaCode(706)

I'm getting null results. The table has lots of 706 area codes.

If the table has lots of 706 codes, how can @Customer hold them all? Don't you need to return 1 value from the select? — Turophile
– Turophile, Commented Feb 11, 2014 at 1:09
Have you tried executing the select statement outside of the function to see if it works? — Turophile
– Turophile, Commented Feb 11, 2014 at 1:11
It doesn't, it gives me no column name and no rows affected. And you're right, about @customer, I didn't think of that... but apparently I'm not even making it to that problem yet. — Aarron Dixon
– Aarron Dixon, Commented Feb 11, 2014 at 1:16
You are filtering on left 3 chars of PostalCode (a.k.a. zip code), I think you meant to use left 3 chars of Phone Number instead since that is what area code is related too? — BateTech
– BateTech, Commented Feb 11, 2014 at 1:19
So, this isn't a user defined functions problem, it's a select not working problem. Someone else here briefly posted a comment that your joins may not be working - do you have correct matching data in all tables - query each one, working "upwards" to check which values you get, and plugging them into the next query on one table. You may find that the are no rows in CustomerAddress which match for example. — Turophile
– Turophile, Commented Feb 11, 2014 at 1:20

BateTech · Accepted Answer · 2014-02-11 02:43:05Z

1

You are filtering on left 3 chars of PostalCode (a.k.a. zip code), I think you meant to use left 3 chars of Phone Number instead since that is where you find the area code.

Also, you have created a scalar function, so after you change to filter on area code instead of postal code, and if there is more than one customer with the 706 area code, you will likely get the following error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

If you are wanting to retrieve all customers that have a certain area code, then you will need a table value function. See http://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx

If you do not want duplicate customer names returned, then add DISTINCT after the SELECT in your function.

Note that when selecting from a table valued function, you should use the syntax

SELECT Customers FROM dbo.ufnGetCustomerByAreaCode('706')

instead of

SELECT dbo.ufnGetCustomerByAreaCode('706')

answered Feb 11, 2014 at 2:43

BateTech

6,6764 gold badges24 silver badges33 bronze badges

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

2 Comments

BateTech Over a year ago

I had the wrong function name in the select, updated to ufnGetCustomerByAreaCode instead of udfGetCustomerByAreaCode

Aarron Dixon Over a year ago

Yeah I should have caught that... should have caught a lot of things. Thanks for the help everyone, everything things to work now!

Collectives™ on Stack Overflow

Getting a null value not expected for user defined function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related