Gotcha: IsNull()

Whilst working with IsNull() a while ago i came across some strange behaviour that made total sense once i ventured into books online, however it was suitably tricky that I thought it worthy of a Gotcha post.

You see, i was retrieving some text fields which may have, legitimately, been null, in cases of null I wanted to display that the field was not yet entered. The trouble I had though is that the default text was being truncated.

In this (contrived) example, if the source school is null we want to display ‘Not Yet Registered’ , however when the query is run the text is truncated to ‘Not Yet Registe’.

image

The TSQL behind this is as follows:

declare @Student table
        (StudentID int primary key, 
        StudentName varchar(30) not null,
        SourceSchool varchar(15) null)

insert into @Student(StudentID, StudentName, SourceSchool)
values    (231, 'Joe Bloggs', 'Lake Kytin'),
        (346, 'Mary Lilley', 'Port Smithton'),
        (564, 'Sue Cambell', null)

select 
    StudentID,
    StudentName,
    isnull(SourceSchool, 'Not Yet Registered') SourceSchool
from
    @Student

So why is this happening, well, a quick look at Books Online shows that the return type of IsNull() is actually that of the field in the check expression:

ISNULL ( check_expression , replacement_value )

So in our code above, even though both the constant field in the replacement value (“Not Yet Registered”) and the check expression itself are both string fields, the check expression is varchar(15) and the replacement value has a length of 18. which is automatically truncated to 15.

BOL Entry for IsNull()

So in order to correct this we can:

1) increase the length of the SourceSchool field

-or-

2) reduce the length of the replacement value

-or-

3) Cast the check expression to the data type of the replacement value.

Option 1 is changing the schema based on reports which is not ideal, Option 2 is not valid as the text length may be a requirement so Option 3 it is.

This is shown below:

declare @Student table
        (StudentID int primary key, 
        StudentName varchar(30) not null,
        SourceSchool varchar(15) null)

insert into @Student(StudentID, StudentName, SourceSchool)
values    (231, 'Joe Bloggs', 'Lake Kytin'),
        (346, 'Mary Lilley', 'Port Smithton'),
        (564, 'Sue Cambell', null)

select 
    StudentID,
    StudentName,
    isnull(cast(SourceSchool as varchar(18)), 'Not Yet Registered') SourceSchool
from
    @Student
As we can see, the results are now what we would expect

image

Advertisements
Categories: Gotcha, TSQL | Tags: | Leave a comment

Post navigation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: