Using CONVERT() within a case statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using CONVERT() within a case statement

Author

Topic

davenims
Starting Member

21 Posts

Posted - 2006-10-17 : 11:27:30

Can anyone tell me what I'm doing wrong here? This is driving me up the wall - it seems to be absolutely fine to me, but when I use a CONVERT() function within one of the cases, it always processes the last case in the statement.


SELECT CASE @DecimalPlaces 
  WHEN 1 THEN CONVERT(Decimal(8,1),@StartValue) 
  WHEN 2 THEN CONVERT(Decimal(8,2),@StartValue) 
  WHEN 3 THEN CONVERT(Decimal(8,3),@StartValue) 
  WHEN 4 THEN CONVERT(Decimal(8,4),@StartValue) 
  WHEN 5 THEN CONVERT(Decimal(8,5),@StartValue) 
  ELSE CONVERT(Decimal(8,0),@StartValue)
END AS FormattedValue

For example, if @DecimalPlaces is 2 and @StartValue is 0.5, it will always return 0.50000.

I've tried rewriting it the long way (i.e. CASE WHEN @DecimalPlaces = 1 THEN... WHEN @DecimalPlaces = 2 THEN... etc), but it does exactly the same.

Ideally what I'd like to do is just write CONVERT(Decimal(8,@DecimalPlaces),@StartValue), but SQL Server doesn't seem to like that, hence this function.

davenims
Starting Member

21 Posts

Posted - 2006-10-17 : 11:33:30

Update - it doesn't go for the last case, it goes for the one with the largest scale. If I change one of the statements in the middle to

WHEN 3 THEN CONVERT(Decimal(8,7),@StartValue)

then it will choose that one...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 11:38:28

The scenario you explained did not happen when I ran it. It's running perfectly fine:

set @decimalplaces = 1
set @StartValue = 10.57645

output
---------
10.60000

set @decimalplaces = 2
set @StartValue = 10.57645

output
---------
10.58000


set @decimalplaces = 3
set @StartValue = 10.57645

output
---------
10.57600

Harsh Athalye
India.
"Nothing is Impossible"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-17 : 11:47:38

The problem is that SQL Server is rounding correctly as harsh pointed out, but it has to decide up front what type to use for the column and its making that as wide as the widest possible result.

You can do this with dynamic SQL like this:

DECLARE @DecimalPlaces int
DECLARE @StartValue decimal(18, 9)
DECLARE @sql varchar(1000)
SELECT @DecimalPlaces = 5, @StartValue = 0.5
SET @sql = 'SELECT CONVERT(Decimal(8,' + CONVERT(varchar(10), @DecimalPlaces) + '),' 
	+ CONVERT(varchar(19), @StartValue) + ') AS FormattedValue' 
EXEC(@sql)

davenims
Starting Member

21 Posts

Posted - 2006-10-17 : 11:51:59

OK, that's maybe explaining the problem anyway. I'm using a CONVERT(Varchar, ...) on the value outputted from this, and I need it just to show the number of decimals that I have specified... for example, if I put it in 0.5412 with 1 decimal place, I need it to show 0.5, not 0.50000

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-17 : 13:38:53

If this is part of a query result, a column can only have one datatype, so you will have to select one. If you have to be able to select the precision of a column at run time, you can use dynamic SQL.

You can use the ROUND function to round off to different levels of precision.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources