CODEWITHSUNDEEP
t-sqluser-defined-functionssql-server-2017
t3chb0t
t3chb0t

Reputation: 18754

Scalar-valued function does not return NULL but a 'NULL' string

I need to import data from Excel into a ms sql database and I thought using the OPENROWSET would be a good idea... well, it is not bad but has some side effects.

The data I'm receiving is not allways 100% correct. By correct I mean cells that should be NULL (and in Excel empty) sometimes contain the string "NULL" or some other junk like whitespaces. I tried to fix it with this script:

GO    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[NullIfEmpty](@input nvarchar)
RETURNS nvarchar(max)
AS
BEGIN

    if (@input = '' or @input = 'NULL')
    begin
        return NULL
    end

    return @input

END

But strange things happen. This gives me a string with the text "NULL" instead of a real NULL so the grid cell after querying the database isn't yellow but contains normal text even though the target column allows NULL.

A simple test with:

select dbo.nullifempty('NULL')

or

select dbo.nullifempty(null)

also yields a string.

Do you know why this is happening and how I can fix it?

Upvotes: 1

Views: 868

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82554

To get null for empty strings or strings that are the word NULL, you could just use coalesce and nullif:

COALESCE(NULLIF(@input, 'NULL'), NULLIF(@Input, ''), @input)

Please note that the problem in your original code is because you didn't specify the length of the @input parameter - so SQL Server created it as varchar(1).
You should always specify length for char/varchar/nchar and nvarchar.

From nchar and nvarchar page remarks:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

(n referring to the n in nchar(n) or nvarchar(n))

Upvotes: 2

Vasily
Vasily

Reputation: 5782

you should reassign value for declared variable using set

''''
BEGIN

    if (@input = '' or @input = 'NULL')
    begin
        set @input = NULL
    end

    select @input

END

test

enter image description here

Upvotes: 1

pi.314
pi.314

Reputation: 620

repleace lines with 'ALTER"

ALTER FUNCTION [dbo].[NullIfEmpty](@input nvarchar(max))

and with line with 'if'

if (LTRIM(RTRIM(@input)) = '' or @input IS NULL)

Upvotes: 2

Related Questions