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.
Cast with Dynamic Precision - Error
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):-
cast with variable scale - Sytax of STR function
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse
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:-
Cast with variable scale - SQL Server - STR Function
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.
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.