[FIXED] How to set decimal precision dynamically using a variable

Issue

I am trying to set decimal precision dynamically in a stored procedure like as below

 DECLARE @precison int
 select @precison = [ConfigValue] from [TestData] WHERE ConfigName = 'Precision'
 select CAST( (10.56/10.25)as decimal(18, @precision))

If precision is '2' SP should return '1.03' as result.But cast function is not accepting @precision parameter as variable .I want to return data as decimal itself from stored procedure.How can i achieve this in SQL Server

Solution

The problem here is you have entirely the wrong idea. Formatting of data should be handled in the Presentation layer (in this case SSRS) not the RDBMS. Though you could dynamic declare something with a dynamic precision or scale, you would have to use dynamic SQL and that could severely overly complicate the matter and would likely break your SSRS report unless you understand how you implement dynamic statements successfully into SSRS.

Instead, you should be using the presentation layer to determine the formatting. This isn't particularly easy in SSRS, but it's also not difficult. Lets assume you have a parameter, Precision, in your SSRS report, which is defined as an Integer. I'm also going to use a basic data set from the below SQL:

SELECT d
FROM (VALUES(10),(10.1),(10.12),(10.123),(10.1234))V(d);

This means you might have a report that looks a little like this:

Right click the blank space, and select Report Properties and go to the Code pane. Then enter the below code:

Public Function DynamicPrecision(ByVal Precision AS Integer) AS String

    Dim I AS Integer
    If Precision = 0
        DynamicPrecision = "#,#"
    Else
        DynamicPrecision = "#,#."
        For I = 1 to Precision
            DynamicPrecision = DynamicPrecision & "0"
        Next I
    End If
End Function

Now click your cell (or hold control and click the cells) you want the dynamic precision on and then press F4 to open the properties pane. Fine Format in the Properties Pane and then then drop down and select "«expression»". Then enter the following expression:

=Code.DynamicPrecision(Parameters!Precision.Value)

Your cell will then dynamic format to that precision.

So, for 0,2, and 4, the data would look like this:

Answered By - Larnu

Answer Checked By - Mildred Charles (PHPFixing Admin)