CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008sql-server-2005
Mysterious Jack
Mysterious Jack

Reputation: 641

How to create decimal variable dynamically in SQL Server

--Actually this is calculated based on some logic but here
-- I have hard coded it to keep it simple.    
DECLARE @Count INT = 4  

--To accommodate Precision + Scale correctly
SET @Count = @Count + 10 

-- I want something like this
DECLARE @Temp DECIMAL( @Count , 10 )

I want to create a decimal variable in SQL Server whose Precession is taken from some variable dynamically, I have tried the above snippet but it fails with error

'Incorrect syntax near '@Count'.'

Is there any possible solution to do some thing like that ?

EDIT:

I want the requirement like that because I have to overcome the problem which am facing in the below snippet:

DECLARE @Var1 DECIMAL(32, 10) = 0.0000001700
DECLARE @Var2 DECIMAL(32, 10) = 1.0000000000

Select CAST( (@Var1*@Var2) AS DECIMAL(32,10) ) --This Outputs 0.0000000000

Now the above snippet outputs 0.0000000000 instead of 0.0000001700, Hence in order over come the problem I want to create a new variable whose precession would be the number of digits of @var2 which are before decimal point @newVar Decimal(11,10) and then perform multiplication to get correct output.

DECLARE @Var2 DECIMAL(32, 10)= 1.0000000000
DECLARE @newVar DECIMAL( 11 , 10 )= @Var2 -- here 11 is dynamically calculated, my original question

DECLARE @Var1 DECIMAL(32, 10)= 0.0000001700

Select CAST((@Var1*@newVar) AS DECIMAL(32,10)) --This correctly outputs 0.0000001700

Note: I know decreasing the precession and scale of original variables @var1 and @var2 would solve the problem, but in my project it will cause big architectural change, hence it cant be done.

Upvotes: 1

Views: 5290

Answers (2)

Martin Smith
Martin Smith

Reputation: 453328

The easiest way would be to cast to float instead.

DECLARE @Var1 DECIMAL(32, 10) = 0.0000001700;
DECLARE @Var2 DECIMAL(32, 10) = 1.0000000000;

DECLARE @Result DECIMAL(38, 10) = CAST(@Var1 AS FLOAT) * CAST(@Var2 AS FLOAT);
SELECT @Result /*0.0000001700*/

If for some reason that isn't suitable the next easiest is dynamic SQL though it could be done statically with a 253 branch CASE statement for all the (22* 23)/2 possibilities. 

DECLARE @Var1 DECIMAL(32, 10) = 0.0000001700;
DECLARE @Var2 DECIMAL(32, 10) = 1.0000000000;

DECLARE @Result DECIMAL(38, 10);

DECLARE @DynSQl NVARCHAR(MAX) = '
SET @Result = CAST(@Var1 AS DECIMAL(' + LTRIM(10 + LEN(ABS(CAST(FLOOR(@Var1) AS INT)))) + ',10)) * CAST(@Var2 AS DECIMAL(' + LTRIM(10 + LEN(ABS(CAST(FLOOR(@Var2) AS INT)))) + ',10)) 
';

EXEC sp_executesql
  @DynSQl,
  N'@Var1 DECIMAL(32, 10), @Var2 DECIMAL(32, 10), @Result DECIMAL(38, 10) OUTPUT',
  @Var1 = @Var1,
  @Var2 = @Var2,
  @Result = @Result OUTPUT;

SELECT @Result; /*0.0000001700*/

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269963

I don't see any obvious reason for creating a decimal value with a dynamic precision or scal. One work-around is to create a decimal value that is "big enough". Then you can use str() to output it to whatever precision you like.

So:

declare @temp decimal(38, 10);

select str(@temp, @count + 10 + 1);

If you are doing this for a column in a table, you can even make the expression a calculated column.

Upvotes: 0

Related Questions