SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Calculation using CASE and apostrophes		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Henrik Svensson
Starting Member

Sweden
25 Posts
Posted - 02/13/2013 :  04:09:07  Show Profile  Reply with Quote
Hi!

I have a question. I know this doesn't seem like "good SQL programming", but I came across some complex piece of code that was constructed in a similar way as my simplified exemples below, that made an incorrect calculation, and would really appreciate if anyone might explain to me what causes this behaviour, i.e. what is the explanation to why SQL Server gives me different results when running the two queries below?


SELECT 100.00 * 
  CASE 
    WHEN 1=1 THEN '0.55000'
	ELSE 1.0
  END 

SELECT 100.00 *
  CASE 
    WHEN 1=1 THEN '0.55000'
	ELSE '1.0'
  END


With best regards,
Henrik Svensson
Edited by - Henrik Svensson on 02/13/2013 04:12:05

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 02/13/2013 :  04:31:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See the output of this to understand


select '0.55000'+1.0,'0.55000'+1.00



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/13/2013 :  08:27:24  Show Profile  Reply with Quote
To add to what Madhivanan said: run this query and you will see that the data types returned from the case expression are are different.
SELECT SQL_VARIANT_PROPERTY(
  CASE 
    WHEN 1=1 THEN '0.55000'
	ELSE 1.0
  END,'BaseType') 

SELECT SQL_VARIANT_PROPERTY(
  CASE 
    WHEN 1=1 THEN '0.55000'
	ELSE '1.0'
  END ,'BaseType')
You can also look at the precision and scale of the case expression of the numeric type and the maxlength of the varchar type to get a better understanding.

All of that comes about because the data types of each when clause in a case expression has to be of the same or it should be possible to make them so by an implicit conversion. In your first example, an implicit conversion is required because the when expression is of varchar type and the else is of decimal type. Whether it gets converted to varchar or decimal is dependent on the conversion precedence, and decimal happens to be higher in the pecking order than varchar in that regard.
Go to Top of Page

Henrik Svensson
Starting Member

Sweden
25 Posts
Posted - 02/13/2013 :  08:39:49  Show Profile  Reply with Quote
Ok.

Thank you for your answers!

With best regards,
Henrik Svensson
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000