CASE condition not affecting CAST output

Question

Post reply

CASE condition not affecting CAST output

greg.bull

SSCrazy

Points: 2093
More actions
June 10, 2014 at 8:40 am

#396890

Hi,
I've a variable in my SP that I'd like to use to change the format of my output. Try this!
select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Then this:
select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Can anyone help me understand (and address) why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):
select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2
Work fine. Ah well. Perhaps there is another way...
Regards Greg.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Greg

The CASE expression returns a result with the highest precedence data type of the all the result expressions. I'm not sure how you get round that.

John

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

A CASE statement can only return one type of data. All possible returned values should be the same. Imagine that you have something more extreme such as

select case when datatype='int' then cast(20140610 as int)

when datatype='char' then cast(20140610 as char(8))

when datatype='date' then cast(20140610 as date) end

FROM (SELECT 'int' AS datatype, '20140610' AS value UNION ALL

SELECT 'char' AS datatype, '20140610' AS value UNION ALL

SELECT 'date' AS datatype, '20140610' AS value)x

You can't have a column with several data types. Each column must have a single data type.

Why would you want to go against basic database theory?

For format issues, you should handle them in the front end.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

greg.bull SSCrazy Points: 2093 More actions · Answer 3

Thanks Chaps,

The data is ultimately passed back to the front end as a string, it'll look like this, either

10:5:2

or

12.50:16.50:10.25

I need to format each number before joining them in a string. Each string goes into a datagrid cell. There's no way to format or change this in the front end, hence the need to sort it out in SQL beforehand.

There is a way to do it, *I think*, but it's cumbersome. It'd mean having 2 blocks of code triggered by my variable that controls output format. Anyone have a better idea?

Regards, Greg.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Or you could use a single data type such as a (var)char.

SELECT CASE WHEN decimals = 0

THEN LEFT( CAST( CAST( number AS decimal(10,2)) AS varchar(11)), LEN(CAST( CAST( number AS decimal(10,2)) AS varchar(11))) - 3)

ELSE CAST( CAST( number AS decimal(10,2)) AS varchar(11)) END

FROM (VALUES(10, 0),(5, 0),(2, 0),(12.50, 2),(16.50, 2),(10.25, 2))x(number,decimals)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

greg.bull SSCrazy Points: 2093 More actions · Answer 5

Thanks for this. The query is quite simple, so I'm going to do it like this:

declare @route int

set @route = 2

if @route = 1

begin

select cast(1 as decimal(10,2)) as greg

end

if @route = 2

begin

select cast(1 as decimal(10,0)) as greg

end

Sean Lange SSC Guru Points: 286589 More actions · Answer 6

greg.bull (6/10/2014)
Thanks for this. The query is quite simple, so I'm going to do it like this:
declare @route int
set @route = 2
if @route = 1
begin
select cast(1 as decimal(10,2)) as greg
end
if @route = 2
begin
select cast(1 as decimal(10,0)) as greg
end

You might want to read this article about multiple execution paths. It can greatly affect performance unless you handle it correctly.

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

greg.bull SSCrazy Points: 2093 More actions · Answer 7

greg.bull

SSCrazy

Points: 2093

June 10, 2014 at 10:11 am

#1720489

Thanks Sean, will do.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 8

It's a bit strange to see people boldly stating that you cannot do this, when you can, you just need to cast them as SQL_VARIANT.

I'm not saying you should do this....

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);