Get Decimal Scale Based on Variable Value

In recent past, while designing a report mailer I faced a requirement where I had to convert a float column into DECIMAL with dynamic precision value coming from outside of procedure as a parameter.

Below is the sample of requirement.

I instantly realized my mistake. Precision & Scale values while using cast/convert must be Constant. So there is no possibility of using variable value with Cast/Convert functions. Then how do I cast it with variable Scale? The first solution that came to my mind is Dynamic SQL. Dynamic SQL is life saver many a times. But thanks to vast library of SQL Server, we have STR() function. This function introduced with SQL 2008, returns character data converted from numeric data. Below is the syntax (more):-

Unlike DECIMAL data type inside CAST/CONVERT, it accepts variable of decimal datatype and return varchar result. This way it fulfilled my requirement by below code:-

Conclusion: Sometimes even a small piece of function or code saves us hours of time. STR() function did exactly that for me. I hope it saves your time as well. Happy Coding.

Please feel free to correct me by comments. If you liked the article, do Like, Share & Comment.

Leave a Reply