Return nullable type in SQL Server function

Question

Is it possible to create a SQL Server function which returns a nullable string?

I would like to create a function which returns DBNull if the value is 0 or an empty string:

Create FUNCTION [dbo].[SetDBNullNvarChar] (@input nvarchar(1000))
RETURNS (needs to be nullable)
AS
BEGIN
    if (@input = '' OR @input = 0) 
    BEGIN
    RETURN null
    END
return @input
END

Answer 1

Any data type in SQL can be set to null unless a not null restriction is put on it. Therefore you should be able to use whatever return type best suits your needs...

In your calling code, you'd have to check to see if the returned value is equal to DBNull.Value and have your code act accordingly.

Answer 2

This does return NULL for me:

Create FUNCTION [dbo].[SetDBNullNvarChar] (@input nvarchar(1000))
RETURNS NVARCHAR
AS
BEGIN
    if (@input = '' OR @input = 0) 
    BEGIN
    RETURN null
    END
return @input
END

SELECT  [dbo].[SetDBNullNvarChar](0)