Posts Tagged With: IsNull

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

Blog at WordPress.com.