Null parameter to function doesn't seem to be processed

8 Jul 2009 9:13 AM

Rotwatcher

I have a function that takes a bit parameter and returns a text
substitute. I use it in this kind of operation:

SELECT dbo.fnReturnYesOrNo(MyTable.BitColumn) AS YesOrNo FROM MyTable

However, if any values of MyTable.BitColumn are NULL then it returns
NULL instead of 'Neither'.

I can wrap it in an ISNULL to get the 'Neither' - e.g.

SELECT ISNULL(dbo.fnReturnYesOrNo(MyTable.BitColumn), 'Neither') AS
YesOrNo FROM MyTable

Can I "persuade" the function to return 'Neither' if the value of the
input parameter is NULL?

Thanks

Edward

CREATE FUNCTION [dbo].[fnReturnYesOrNo]
(
    -- Add the parameters for the function here
    @YesNoValue int
)
RETURNS varchar(10)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(10)

    -- Add the T-SQL statements to compute the return value here
    SELECT @Result = CASE @YesNoValue WHEN 1 THEN 'Yes' WHEN 0 THEN 'No'
WHEN NULL THEN 'Neither' WHEN Null THEN 'Neither' END

    -- Return the result of the function
    RETURN @Result

END

8 Jul 2009 9:43 AM

Uri Dimant

Hi
--A UDF that doesn't reference a db table

create function dbo.udf (@t bit)

returns varchar(100)

as

begin

DECLARE @i AS varchar(10)

select @i =case when @t is null then 'neither' else 'text' end

return @i

end

select dbo.udf(1)

select dbo.udf(null)

Show quoteHide quote

"Rotwatcher" <edwardw***@googlemail.com> wrote in message
news:80a997b6-b1ae-483e-b41e-355dd7b0732f@24g2000yqm.googlegroups.com...
>I have a function that takes a bit parameter and returns a text
> substitute. I use it in this kind of operation:
>
> SELECT dbo.fnReturnYesOrNo(MyTable.BitColumn) AS YesOrNo FROM MyTable
>
> However, if any values of MyTable.BitColumn are NULL then it returns
> NULL instead of 'Neither'.
>
> I can wrap it in an ISNULL to get the 'Neither' - e.g.
>
> SELECT ISNULL(dbo.fnReturnYesOrNo(MyTable.BitColumn), 'Neither') AS
> YesOrNo FROM MyTable
>
> Can I "persuade" the function to return 'Neither' if the value of the
> input parameter is NULL?
>
> Thanks
>
> Edward
>
> CREATE FUNCTION [dbo].[fnReturnYesOrNo]
> (
> -- Add the parameters for the function here
> @YesNoValue int
> )
> RETURNS varchar(10)
> AS
> BEGIN
> -- Declare the return variable here
> DECLARE @Result varchar(10)
>
> -- Add the T-SQL statements to compute the return value here
> SELECT @Result = CASE @YesNoValue WHEN 1 THEN 'Yes' WHEN 0 THEN 'No'
> WHEN NULL THEN 'Neither' WHEN Null THEN 'Neither' END
>
> -- Return the result of the function
> RETURN @Result
>
> END

8 Jul 2009 10:33 AM

Rotwatcher

On Jul 8, 10:43 am, "Uri Dimant" <u***@iscar.co.il> wrote:
> Hi
> --A UDF that doesn't reference a db table

Thanks - that worked fine.

Edward