create function

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!john wrote on Fri, 9 Jun 2006 10:09:54 -0400:

> 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|||Just tried it on SQL Server 2000 too, works fine.
Dan|||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" <msnews@.worldofspack.com> wrote in message
news:uKXChC9iGHA.4344@.TK2MSFTNGP05.phx.gbl...
> Just tried it on SQL Server 2000 too, works fine.
> Dan
>|||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
"john doe" <jdoe@.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!
>|||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:
> 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" <msnews@.worldofspack.com> wrote in message news:uKXChC9i
GHA.4344@.TK2MSFTNGP05.phx.gbl...|||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:
> 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" <msnews@.worldofspack.com> wrote in message news:uKXChC9i
GHA.4344@.TK2MSFTNGP05.phx.gbl...