Using a column value for the decimal scale value

Question

Using a column value for the decimal scale value

Laurie N Lemons

SSC Journeyman

Points: 89
More actions
June 3, 2010 at 8:34 am

#221704

I have a column in a table called DecimalPrecision and the datatype is int. I am trying to use the value of DecimalPrecision as the scale in a CAST AS DECIMAL statement and am having no luck.
Here is my example:
CAST(QueryRefValue1 AS DECIMAL(20,DecimalPrecision)) AS ReferenceLow
Here is my error message:
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'DecimalPrecision'.
Does anyone have any suggestions on how to go about this.
Thanks......

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

Laurie, you can do this dynamically, but I don't know why you would want to do this. Here's an example of how to do it dynamically

declare @vn_precision int,

@vs_sql nvarchar(200)

set @vn_precision = 3

set @vs_sql = 'declare @vs_sql nvarchar(200)

set @vs_sql = ''select cast(100.3456789 as decimal(20,' + CAST(@vn_precision as nvarchar) + '))''

exec sp_executesql @vs_sql'

exec sp_executesql @vs_sql

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Laurie N Lemons SSC Journeyman Points: 89 More actions · Answer 2

Thanks for the response, Mike....but I don't want the precision to be a fixed number, like 3. I want the precision to dictated by a column value. Sorry if I just don't understand, but thanks again for your response.

Mike01 SSChampion Points: 11395 More actions · Answer 3

this gets the value from a table

DECLARE @tbl TABLE(PrecisionID int, PrecisionNum int)

INSERT INTO @tbl

SELECT 10, 1 UNION ALL

SELECT 11, 2 UNION ALL

SELECT 12, 3 UNION ALL

SELECT 13, 4;

declare @vn_precision int,

@vs_sql nvarchar(200)

-- get Precision Number from table

select @vn_precision = PrecisionNum

from @tbl

where PrecisionID = 11