Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Twist in ISNULL function

By Ashutosh Pandey,

ISNULL is a commonly used function in SQL Server used to substitute alternate value if the one being checked is “NULL”. However there is a stumbling block especially while using this function on values that are strings (CHAR/VARCHAR).

Background:

Let us consider a questionnaire report with typical questions like:-

----------------------------------

1.     What is your name?

2.     What is your age?

3.     Would you recommend our product to your friends and relatives?

4.     Did you like our services?

-----------------------------------

The underlying table for this report contains the data row wise i.e. each row holds the response for a single question. The set of questions and their answers go into the feedback table.

Sample Code to create Feedback:

CREATE TABLE Feedback (
  ProductID int,
  FeedbackQues varchar(1000),
  FeedbackAns varchar(255)
)

INSERT INTO Feedback
SELECT 1,'What is your name','Abc'
UNION
SELECT 1,'What is your age','25'
UNION
SELECT 1,'Would you recommend our product to your friends and relatives','Yes'
UNION
SELECT 1,'Did you like our services','Yes'
UNION
SELECT 1,'Was our call agent humble and helpful to you','Yes'

The report that needs to be produced should have the questions in the column headers and the responses in the data cells.

Original Report:

Agent ID

Product ID

SaleDate

What is your name?

What is your age?

Would you recommend our product to your friends and relatives?

Did you like our services?

11

A-010

17-Oct-10

Abc

45

Yes

Yes

11

A-015

17-Oct-10

Aaa

35

No

No

11

A-005

17-Oct-10

Abb

35

Yes

Yes

In order to simplify this report, let us consider alternate descriptions for the questions, which are short.

Agent ID

Product ID

SaleDate

Name

Age

Recommendation probability

Services feedback

11

A-010

17-Oct-10

Abc

45

Yes

Yes

11

A-015

17-Oct-10

Aaa

35

No

No

11

A-005

17-Oct-10

Abb

35

Yes

Yes

To achieve this, an ad-hoc mapping to the original questions can be created to store the short form of these questions.

Sample Code to create temporary mapping table:

SELECT 'What is your name' AS FeedbackQuesDesc,'Name' AS QuesSummary into #QuesMapping
UNION
SELECT 'What is your age','Age'
UNION
SELECT 'Would you recommend our product to your friends and relatives','Recommendation probability'
UNION
SELECT 'Did you like our services','Services feedback'

These questions and their responses are fed through a front end interface by the agent handling the customer. Hence, there may be a question popping up that is not there in the pre-decided list. For example:

“Was our call agent humble and helpful to you?”

The new question may not have a short description associated with it as soon as it is created.

In order to make sure that the report never resulted in a blank header (even if the short form of the question was not defined), the function ISNULL was used in the form:

ISNULL(M.QuesSummary,F.FeedbackQues)]

The query checks if the short description (QuesSummary) has no value, the actual text of the question should be returned. If not, the report should be produced with the short description.

In other words having a short description is preferred but not mandatory.

The short question from mapping table (M.QuesSummary) and in case the short form is missing the ISNULL function would display the complete original question (F.FeedbackQues).Sample code given below.

Predicament

On executing the query 

-- Selecting short question from the mapping table, 
-- if unavailable then showing the complete question from the 
-- Feedback table using the ISNULL function

SELECT 
  ISNULL(M.QuesSummary,F.FeedbackQues) AS Question,
  F.FeedbackAns AS Answer
 FROM Feedback F
   LEFT OUTER JOIN #QuesMapping M 
     ON M.FeedbackQuesDesc=F.FeedbackQues

Output expected:

Services feedback

Yes

Was our call agent humble and helpful to you

Yes

Age

25

Name

Abc

Recommendation probability

Yes

However, the actual output is not as per the expectations.

Actual output:

Services feedback

Yes

Was our call agent humble

Yes

Age

25

Name

Abc

Recommendation probability

Yes

As you can see, since no Short text was defined for the question “Was our call agent humble and helpful to you”, the expected result should have been the entire text of the question. However, the result received is truncated!

Analysis:

It seems that ISNULL function was not working the way one would expect it to.

To confirm this, let’s try a simple code to recreate the issue:-

declare @a varchar(100)
declare @b varchar(10)

set @a='My name is Ashutosh and you are reading an article on ISNULL function'

set @b=NULL

select ISNULL(@b,@a)

Output:

My name is

This confirmed that ISNULL function is type casting @a into @b returning a string equal to the length of variable @a. On checking at MSDN (http://msdn.microsoft.com/en-us/library/aa933210(v=sql.80).aspx), I read the following:

Syntax

ISNULL check_expression , replacement_value )

Arguments

check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion..

Solution:

To resolve the issue, I type casted the check _expression in the replacement_value as shown

ISNULL(CONVERT(VARCHAR(8000),M.QuesSummary),F.FeedbackQues)

This makes the entire query look like this:

SELECT ISNULL(CONVERT(VARCHAR(8000),M.QuesSummary),F.FeedbackQues) AS Question,F.FeedbackAns AS Answer,M.QuesSummary,F.FeedbackQues
FROM Feedback F
LEFT OUTER JOIN #QuesMapping M ON M.FeedbackQuesDesc=F.FeedbackQues

Endgame:

Whenever you use ISNULL function, keep in mind the replacement_value should be of the same type and length as of the check_expression. In case they are different then explicitly type cast check_expression into replacement_value 

You can also use the COALESCE function to avoid the trap.

Total article views: 24957 | Views in the last 30 days: 5
 
Related Articles
FORUM

ISNULL QUESTION

is Not ISNULL(...) the opposite of ISNULL?

FORUM

ISNULL

Comments posted to this topic are about the item [B]ISNULL[/B] Thanks for the question Interesting q...

ARTICLE

Feedback

Corporate management often asks for feedback and buy-in with their ideas and programs. Why don't the...

FORUM

Feedback

Comments posted to this topic are about the item [B]Feedback[/B] Oh my, Steve - that's a big can of ...

FORUM

Contrary of ISNULL() ?

somthing like that: SELECT *, ISNOTNULL(Authordate,'Last Upload: ') + ISNULL(Authordate,'') From Do...

Tags
isnull    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones