create function

9 Jun 2006 2:09 PM

john doe

I'd like to return a NULL value from a mssql function, but i can't get it
to. here's my sample:

CREATE FUNCTION dbo.FixVarChar (@val nvarchar(30) )
RETURNS nvarchar(30) AS
BEGIN
declare @retval as varchar (30)
select @retval = case when len(@val)>0 then @val else null end
return (null)
END

it does NOT return a null value. when I run:

select dbo.fixvarchar('somevalue') as test

it does not return null but what looks like a zero-length string.

i actually need to get the function to return the value (if the string has
len>0) or an actuall NULL value.

help!

9 Jun 2006 2:24 PM

Daniel Crichton

john wrote on Fri, 9 Jun 2006 10:09:54 -0400:

Show quoteHide quote

> I'd like to return a NULL value from a mssql function, but i can't get it
> to. here's my sample:
>
> CREATE FUNCTION dbo.FixVarChar (@val nvarchar(30) )
> RETURNS nvarchar(30) AS
> BEGIN
> declare @retval as varchar (30)
> select @retval = case when len(@val)>0 then @val else null end
> return (null)
> END
>
> it does NOT return a null value. when I run:
>
> select dbo.fixvarchar('somevalue') as test
>
> it does not return null but what looks like a zero-length string.
>
> i actually need to get the function to return the value (if the string has
>

len>> 0) or an actuall NULL value.

>
> help!

I've just created your function on my SQL Server 2005 machine, and run the
same select, and get a NULL (when run in Query Analyzer) - and you'll always
get NULL too, unless you fix your last line to be RETURN (@retval). What
version of SQL Server are you trying this on?

Dan

9 Jun 2006 2:25 PM

Daniel Crichton

Just tried it on SQL Server 2000 too, works fine.

Dan

9 Jun 2006 4:55 PM

john doe

it's sql 2000.

I put that last line in there just to ensure i was returning a null value.
(ultimately, the function will return the non-null value if it exists or
null. I have an xml export program that expects null for non-existing
element nodes to be created).

Show quoteHide quote

"Daniel Crichton" <msn***@worldofspack.com> wrote in message
news:uKXChC9iGHA.4344@TK2MSFTNGP05.phx.gbl...
> Just tried it on SQL Server 2000 too, works fine.
>
> Dan
>

12 Jun 2006 7:38 AM

Daniel Crichton

As I said, I tried it on SQL 2000 here, worked fine (returned a null as it
was, returned the value I passed in when I adjusted it to return @retval,
and a null if the passed in value was a blank string).

Dan

john wrote on Fri, 9 Jun 2006 12:55:24 -0400:

Show quoteHide quote

> it's sql 2000.
>
> I put that last line in there just to ensure i was returning a null value.
> (ultimately, the function will return the non-null value if it exists or
> null. I have an xml export program that expects null for non-existing
> element nodes to be created).
>
> "Daniel Crichton" <msn***@worldofspack.com> wrote in message news:uKXChC9iGHA.4344@TK2MSFTNGP05.phx.gbl...
>> Just tried it on SQL Server 2000 too, works fine.
>>
>> Dan
>>

12 Jun 2006 7:39 AM

Daniel Crichton

Forgot the ask something else in my reply - how are you testing the return
value? I used query analyser where it shows NULL in the column, but if
you're using something else maybe that is interpreting nulls as empty
strings. Without more information there's not much else I can suggest.

Dan

john wrote on Fri, 9 Jun 2006 12:55:24 -0400:

Show quoteHide quote

> it's sql 2000.
>
> I put that last line in there just to ensure i was returning a null value.
> (ultimately, the function will return the non-null value if it exists or
> null. I have an xml export program that expects null for non-existing
> element nodes to be created).
>
> "Daniel Crichton" <msn***@worldofspack.com> wrote in message news:uKXChC9iGHA.4344@TK2MSFTNGP05.phx.gbl...
>> Just tried it on SQL Server 2000 too, works fine.
>>
>> Dan
>>

10 Jun 2006 11:13 PM

Dan Guzman

I get 'someval' returned on my SQL 2000 SP4 when I replace 'return (null)'
with 'return (@retval)'. Are you running SP4?

In any case, I see a couple of inconsistencies. You are returning varchar
(30) but the function return data type is nvarchar(30). Also, you are
passing a varchar instead of the nvarchar expected as the function
parameter.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quoteHide quote

"john doe" <j***@doe.com> wrote in message
news:%23IjaZ78iGHA.3440@TK2MSFTNGP02.phx.gbl...
> I'd like to return a NULL value from a mssql function, but i can't get it
> to. here's my sample:
>
> CREATE FUNCTION dbo.FixVarChar (@val nvarchar(30) )
> RETURNS nvarchar(30) AS
> BEGIN
> declare @retval as varchar (30)
> select @retval = case when len(@val)>0 then @val else null end
> return (null)
> END
>
> it does NOT return a null value. when I run:
>
> select dbo.fixvarchar('somevalue') as test
>
> it does not return null but what looks like a zero-length string.
>
> i actually need to get the function to return the value (if the string has
> len>0) or an actuall NULL value.
>
> help!
>