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:

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

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:

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

Actual output:

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.