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.
DECLARE @PrecisionValue TINYINT = 2
SELECT CAST(12.2343 AS DECIMAL(12,@PrecisionValue))
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):-
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse
STR ( float_expression [ , length [ , decimal ] ] )
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:-
DECLARE @ScaleValue TINYINT = 2
SELECT STR(12.234,12,@ScaleValue) AS Value
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.