Here is an example of how to use CASE statements in MS SQL to evaluate the size of a number (in this case a file size recorded in a database). Since the file size is numeric in this example, we are going to CAST it into a VARCHAR while we append descriptors to the end, to show "bytes", "kb", and "mb" file size abbreviations. Additionally, we will be using LEFT to remove either 3 or 6 character places from the right side of our file size number.

SELECT   CASE
      WHEN   FileSize < 1000
      THEN   CAST(FileSize AS VARCHAR) + ' bytes'
      WHEN   FileSize >= 1000 AND FileSize < 999999
      THEN   CAST(Left(FileSize, Len(FileSize)-3) AS VARCHAR) + ' kb'
      WHEN   FileSize >= 1000000
      THEN   CAST(Left(FileSize, Len(FileSize)-6) AS VARCHAR) + ' mb'
   END AS FileSize
FROM   SampleTable

If this method is used in a stored procedure, for use with ColdFusion, then we've taken some of the work performed by our application server and given to the SQL server instead. You could just record the abbreviated file sizes and their notations in the database, but you may end up saving a little space in database size storing the numerical representation. Plus, the upload results tend to be calculations in whole numbers, not mixed text.

The CASE statement may not be the best choice or right for every method and occasion, but in configurations where the application server and the SQL server are equally configured (reasonably); making MS SQL do more can help speed up application server processes.

If you find this post useful please leave a comment and let me know how you used the information.