PackPrecScale CLR function

• cmartel 20772

  Ten Centuries

  Points: 1233

  More actions

  December 4, 2018 at 12:01 am

  #378730

  Comments posted to this topic are about the item PackPrecScale CLR function

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  December 4, 2018 at 10:17 am

  #2015473

  Hello Claude. Congrats on publishing your first article on SQL Server Central :-). I think this article is off to a good start, but is incomplete and could use a bit more explanation and examples. For example:

  1. What does "«preemptive» truncation" mean?
  2. What is the output of the initial T-SQL statements? (please see example code below)
  3. What about the rules for precision / scale when it comes to DECIMAL operations? Please see the documentation for "Precision, scale, and Length" (about 1/3 the way down the page).

  4. Why use DECIMAL(38, 18) in the first place?

  5. What do you mean by "culture-independent"? Some of us know, but I suspect many readers won't.
  6. How does one make use of the .NET code shown here? Is it purely a utility for helping with SQLCLR projects? If this is supposed to return a "compacted" T-SQL DECIMAL type, how would you return that from SQLCLR given that the return type (including precision and scale) need to be declared when creating the T-SQL wrapper function? Maybe provide some examples of it being used..

  For the initial T-SQL example code in the article that is trying to explain the problem to the reader, you really should show the output of those statements since many readers will not have SSMS open and hence not be able to execute those statements to see what they produce. Below is an example of how to show the output. I also included an additional variable to show the effect of using a properly sized DECIMAL type:

  DECLARE @D1 DECIMAL(38, 18) = 1.123456789012345678;
DECLARE @D2 DECIMAL(38, 18) = 1.0;
DECLARE @D3 DECIMAL(1, 0) = 1.0;
DECLARE @D4 DECIMAL(20, 18) = 1.123456789012345678;

  SELECT @D1 AS [D1 -- DECIMAL(38, 18)], -- 1.123456789012345678
       @D4 AS [D4 -- DECIMAL(20, 18)]; -- 1.123456789012345678

  SELECT @D1 * @D2 AS [DEC(38, 18) * DEC(38, 18)], -- 1.123457
       @D1 * @D3 AS [DEC(38, 18) * DEC(1, 0)],   -- 1.1234567890123457
       @D4 * @D2 AS [DEC(20, 18) * DEC(38, 18)]; -- 1.123456789012346

  Good luck! Take care, Solomon...

  SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
  Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
  Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
  Info sites — Collations     •     Module Signing     •     SQLCLR