Why sql function return a NULL result?

Question

This is my SQL function

IF OBJECT_ID (N'dbo.limit','FN' ) IS NOT NULL
DROP FUNCTION limit;
Go 
create function dbo.limit(@State_code varchar)
RETURNS nvarchar(max)
AS

BEGIN
DECLARE @ret varchar(max) ;
SELECT @ret = State_Name
FROM aqs_sites
WHERE aqs_sites.State_Code = @State_code and State_Name not like '%guam%'
RETURN @ret ;
END

When I call this function in my select syntax

go
select distinct [dbo].limit(aqs_sites.State_Code) from aqs_sites

I got a NULL result, really have no idea on it. BTW, I use SQL-server 2018.

"I use SQL-server 2018"; that isn't true, SQL Server 2018 doesn't exist. — Thom A
– Thom A ♦, Commented Dec 13, 2018 at 16:28
Why don't you try getting some more data back to debug what value(s) are being passed into the function: select aqs_sites.*, [dbo].limit(aqs_sites.State_Code) from aqs_sites. Also, your return types don't match; the function returns a varchar while the signature says it'll return nvarchar. Why not make them match? — cf_en
– cf_en, Commented Dec 13, 2018 at 16:29
I've posted an answer, however I don't understand why you need the function at all, since both your outer query and function are returning data from the same table. If you want to return guam from result, just add it to the where clause: select distinct state_code from aqs_sites where state_name not like '%guam%' — Daniel N
– Daniel N, Commented Dec 13, 2018 at 16:45
@cf_en well I should make my return type matched, but that's not the point to fix result NULL, thanks anyway — host J
– host J, Commented Dec 13, 2018 at 17:21
@Daniel N I created this function because for instance, if I want to select all the state_name except 'guam' for several quries. — host J
– host J, Commented Dec 13, 2018 at 17:23

Daniel N · Accepted Answer · 2018-12-13 16:57:47Z

1

Either a row in aqs_sites tables has a null value for State_Name or the value you are passing as @state_code parameter does not return any rows.

looking further at the problem, the null values are returned for the rows where state is guam, which is filtered out within the function

edited Dec 13, 2018 at 16:57

answered Dec 13, 2018 at 16:22

Daniel N

1,2181 gold badge9 silver badges14 bronze badges

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

3 Comments

Wolfaloo Over a year ago

if @state_code parameter does not return any rows OR all of the entries yeld true to: State_Name not like '%guam%' the query would not yeld NULL but an empty table which is why there are questions on how to return NULL when no lines are found in SQL

Thom A Over a year ago

@Wolfaloo that's not true. That would only be the case if the table aqs_sites contains no rows. A scalar function returns NULL is no value is returned, as the return variable is declared and will have a value of NULL (provided the return variable doesn't have a default value). db<>fiddle

host J Over a year ago

@Daniel N So in this case, should I change @ state_code into @ State_Name and @ ret = State_Name?

Collectives™ on Stack Overflow

Why sql function return a NULL result?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related