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.
|
1 2 |
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):-
|
1 2 3 |
-- 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:-
|
1 2 |
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.
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.