SQL CASE wrong output

Question

I have this weird encounter using CASE in sql 2014.

This is my query:

  SELECT (CASE WHEN dbo.GetFunctionAge(C.Birthdate) = 0 
               THEN '' ELSE dbo.GetFunctionAge(C.Birthdate)
          END) AS Age
         ,dbo.GetFunctionAge(C.Birthdate)
         ,c.Birthdate
 FROM Client C
 WHERE ClientID = '34d0d845-e3a6-4078-8936-953ff3378eac'

this is the output:

Here is the GetFunctionAge function if you might ask.

IF EXISTS (
  SELECT * 
  FROM dbo.sysobjects 
  WHERE ID = OBJECT_ID(N'[dbo].[GetFunctionAge]') AND 
        xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[GetFunctionAge]

GO

CREATE FUNCTION [dbo].[GetFunctionAge](@BirthDate DATETIME)
RETURNS INT
AS
BEGIN

DECLARE @Age INT

IF(@BirthDate = '1753-01-01 00:00:00.000')
BEGIN
    SET @Age = 0
END
ELSE
BEGIN
    SET @Age = DATEDIFF(hour,@BirthDate,GETDATE())/8766
END

RETURN @Age
END
GO

Question:
Why is Column Age in my output is 0which should be ''?
I added (No column name) to show that its output is 0 so my expected output base from my case condition is '' not 0 I didn't receive any error regarding inconsistency of data so why is case behaving like that?

Thanks for those who could clarify this to me.

You are returning an Int value from dbo.GetFunctionAge(C.Birthdate) which is @Age. Therefore, when you use it in your case statement it returns an int value as well. ' ' becomes 0. — Kim
– Kim, Commented Mar 2, 2016 at 9:57
please try '0' while you are checking in case, may be it is not comparing correctly. — Nitu Bansal
– Nitu Bansal, Commented Mar 2, 2016 at 9:58
1) 2014 has a plain DATE type. 2) Why not DATEDIFF(year, @BirthDate, GETDATE()), instead of using hours? 2b) The hours value is wrong - there's 8760 hours in a (365-day) year. You'd potentially have problems with DST anyways... — Clockwork-Muse
– Clockwork-Muse, Commented Mar 2, 2016 at 9:59

bot · Accepted Answer · 2016-03-03 06:48:35Z

3

SELECT
      (CASE
            WHEN a.ageint = 0 THEN ''
            ELSE cast(a.ageint as varchar(3))
      END) AS Age
    , a.ageint
    , c.Birthdate
FROM Client as C
      CROSS APPLY (
            SELECT
                  ISNULL(dbo.GetFunctionAge(C.Birthdate), 0) AS ageint
      ) AS a
WHERE ClientID = '34d0d845-e3a6-4078-8936-953ff3378eac'
;

edited Mar 3, 2016 at 6:48

bot

4,9313 gold badges45 silver badges67 bronze badges

answered Mar 2, 2016 at 10:10

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bot Over a year ago

Thanks for the answer its working. So Casting is the solution to my problem. But its a bit long compare to the other one. I think the cross apply doesn't need anymore as GetFunctionAge function will never return a null value. But its a good practice to always check with null thank's for it

Paul Maxwell Over a year ago

yes, casting to a string is needed because you use ' ' in that column. The purpose of the cross apply is to avoid repeated calls to that function. Use of isnull or coalesce is optional but if there is any possibility of NULL as a returned result then you may need to use an outer apply. By the way: "length of sql" is not a good measure of quality, short isn't always good (or vice versa).

bot Over a year ago

oh yes i haven't notice it. you use it to avoid repeated calls. is it a good practice to do such thing? sorry im not that familiar yet with cross apply. is it faster than the other answer? when returning result?

Paul Maxwell Over a year ago

Yes it can be faster and yes it can be regarded as good practice for function calls, but you do not have to do it this way. You could use a derived table approach instead.

Kim · Accepted Answer · 2016-03-02 10:07:47Z

2

You can cast it into varchar so you can return ' '.

 SELECT (CASE WHEN dbo.GetFunctionAge(C.Birthdate) = 0 
               THEN '' ELSE Cast(dbo.GetFunctionAge(C.Birthdate) as varchar(5))
          END) AS Age
         ,dbo.GetFunctionAge(C.Birthdate)
         ,c.Birthdate
 FROM Client C
 WHERE ClientID = '34d0d845-e3a6-4078-8936-953ff3378eac'

But If you wish to remain your Age column in data type int.

You could just use NULL instead of ' '

answered Mar 2, 2016 at 10:07

Kim

8258 silver badges25 bronze badges

1 Comment

bot Over a year ago

I'll give this answer the credit as its the simplest, shortest working solution. Casting is the answer to my problem.

Collectives™ on Stack Overflow

SQL CASE wrong output

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related