SQL: Integer Expected error while casting to decimal from a column

Question

This is my SQL and its a snippet from the Select Statement... it is running fine, but now i have to remove the hardcoded values from the CAST ... where i enter the Decimal(38,2) i want that to be replaced from the BaseCurrencyDP.NumberOfDecimalPlaces

But it says Integer expected, and this column NumberofDecimalPlaces in a tinyint.

please guide

select distinct
a,
b,
c,

CASE 
When @userOriginalCurrencyCode IS NOT NULL -- Case For Original Currency Filled in Criteria .
Then SUM(CASE WHEN EmployeeHeader.EmployeePremiumType  = 2 THEN EmployeeAmount.TransactionClosedAmountOriginal ELSE 0 END) 
When @userAccountingCurrencyCode IS NOT NULL
THEN SUM(CASE WHEN EmployeeHeader.EmployeePremiumType  = 2 THEN EmployeeAmount.TransactionClosedAmountAccounting ELSE 0 END ) 
ELSE CASE

WHEN (select COUNT( DISTINCT( EmployeeAmountGroup.OriginalCurrencyCode )) from user.EmployeeAmountGroup  Where EmployeeAmountGroup.EmployeeHeaderID = EmployeeHeader.EmployeeHeaderID )<>1
 THEN                      
    CASE WHEN(select COUNT( DISTINCT( EmployeeAmountGroup.AccountingCurrencyCode )) from user.EmployeeAmountGroup  Where EmployeeAmountGroup.EmployeeHeaderID = EmployeeHeader.EmployeeHeaderID)<>1
       THEN       
            CAST(ROUND(SUM(CASE WHEN EmployeeHeader.EmployeePremiumType  = 2 THEN EmployeeAmount.TransactionClosedAmountBase ELSE 0 END ),BaseCurrencyDP.NumberOfDecimalPlaces) AS Decimal(38,2))
       ELSE CAST(ROUND(SUM(CASE WHEN EmployeeHeader.EmployeePremiumType  = 2 THEN EmployeeAmount.TransactionClosedAmountAccounting ELSE 0 END ),BaseCurrencyDP.NumberOfDecimalPlaces) AS Decimal(38,2))
    END                           
 ELSE CAST(ROUND(SUM(CASE WHEN EmployeeHeader.EmployeePremiumType  = 2 THEN EmployeeAmount.TransactionClosedAmountOriginal ELSE 0 END ),BaseCurrencyDP.NumberOfDecimalPlaces) AS Decimal(38,2))
END
End as TotalClosedAmount,

Please guide

When you say replace 'Decimal(38,2) ' are you trying to dynamically change the number of decimal places using BaseCurrencyDP.NumberOfDecimalPlaces ? — sarin, Apr 06 '14 at 10:04
I'm afraid it means "integer literal expected". Data types can not contain variables. — Gabe, Apr 06 '14 at 10:05
First I was suspicious of passing tinyint to Round function, I've tested it and it's correct `declare @decimals tinyint set @decimals = 2 select round(15.8,@decimals)` Would you tell us the original error message. — Reza, Apr 06 '14 at 10:19
I get the point, the problem is for this kind of query, `declare @decimals tinyint set @decimals = 5 select cast(15.8 as Decimal(38,@decimals))` and it says "Incorrect syntax near @decimals, expecting INTEGER", I am working on it. — Reza, Apr 06 '14 at 10:40

Reza · Answer 1 · 2014-04-06T11:49:48.570

you can use this function to format your data

Create FUNCTION [dbo].[fnFormatCurrency] (@value Decimal(24, 7), @NumberOfDecimals int) 
RETURNS VARCHAR(32) 
AS 
BEGIN 
    DECLARE @temp varchar(38)

    IF @NumberOfDecimals > 0
    BEGIN
        SET @temp = PARSENAME('$'+ Convert(varchar,Convert(money,@value),1),2)
        SET @temp = @temp + RIGHT(str(@value, 32, @NumberOfDecimals) , @NumberOfDecimals + 1)
    END
    ELSE
    BEGIN
        SET @temp = PARSENAME('$'+ Convert(varchar,Convert(money,cast(round(@Value, 0) as int)),1),2)
    END

    RETURN @temp
END

as mentioned in this post http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a6c3745-8a0e-4474-9d76-67bae3f2a040/format-currency-and-percentage-with-variable-number-of-decimal-places?forum=transactsql

or you can use this ugly form query

declare @decimals tinyint
set @decimals = 5
declare @sql as nvarchar(max)
set @sql = 'select cast(15.8 as Decimal(38,'+cast(@decimals as varchar)+' ))'
exec sp_executesql @sql

I hope this helps.

SQL: Integer Expected error while casting to decimal from a column

1 Answers1