ddf_FormattedQty - Formatted Quantities using DECPLQTY

Many of the companies that we work at have items that have varied DECPLQTY - The items might use 0 decimal places for the quantity, but some will use 1, or 2, or more.
This is a very difficult concept for .NET controls to grasp, so usually we just return all five decimal places.

This function will correctly format a decimal quantity to the required number of decimal places, and optionally add in the commas.

The return will be a string.

EDITED 12/23/2016 comma handling for negative numbers

Related Articles

... and you 'll find more on the SQL (General) Menu

-- =============================================
-- returns the item quantity, correctly formatted for the decplqty
-- must supply either itemnmbr or decplqty
-- =============================================
 
IF EXISTS (SELECT *
       FROM   sysobjects
       WHERE  name = N'f_4P_formattedQty3')
    DROP FUNCTION f_4P_formattedQty3
GO
 
 
CREATE FUNCTION f_4P_formattedQty3 (@quantity numeric(19,5),@decplqty smallint, @itemnmbr varchar(31), @bAllowNegative bit, @bShowComma bit )
--  select dbo.f_4P_formattedQty3 (1234567.12345,1,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (123456.12345,1,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (12345.12345,1,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (1234.12345,1,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (123.12345,1,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (12.12345,1,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (1.12345,1,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (1234567.12345,3,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (123456.12345,3,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (12345.12345,3,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (1234.12345,3,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (123.12345,3,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (12.12345,3,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (1.12345,3,'CAROL~BIANCO~QS',1,1)
--  select dbo.f_4P_formattedQty3 (.12345,3,'CAROL~BIANCO~QS',1,1)
 
--  select dbo.f_4P_formattedQty3 (1234567.12345,1,'CAROL~BIANCO~QS',1,0)
--  select dbo.f_4P_formattedQty3 (1234567.12345,3,'CAROL~BIANCO~QS',1,0)
 
Returns varchar(200)
 
AS
begin
    DECLARE @isNegative bit
    declare @out varchar(200)
    declare @decPos int
    declare @commaPos INT
 
    --sometimes we show negative numbers as 0   
    if isnull(@quantity,0) < 0 and @bAllowNegative = 0 begin
        set @quantity = 0
    end
     
    --if the number is negative, set it to positive
    IF @quantity < 0 BEGIN
        Select @isNegative = 1, @quantity = ABS(@quantity)
    END ELSE BEGIN
        SET @isNegative = 0
    end
 
    --get the DECPLQTY if it wasn't supplied   
    if isnull(@decplqty,0) = 0 begin
        select @decplqty = decplqty
            from iv00101 i with (nolock)
            where itemnmbr = @itemnmbr
    end        
     
    --convert to a string
    set @out = convert(varchar(30),isnull(@quantity,0)) 
 
    --find out where the decimal place is
    set @decPos = charindex('.',@out)
 
    --truncate the unwanted decimal places
    set @out = substring(@out,1,@decPos + @decplqty - 1)
 
    --add in the commas
    if @bShowComma = 1 begin
        if charindex('.',@out) > 4 begin
            set @commaPos = charindex('.',@out) - 3
            set @out = substring(@out,1,@commaPos-1) + ',' + substring(@out,@commaPos,999)
        end
        if charindex('.',@out) > 8 begin
            set @commaPos = charindex('.',@out) - 7
            set @out = substring(@out,1,@commaPos-1) + ',' + substring(@out,@commaPos,999)
        end
  
    end    
    if @decplqty = 1 begin
        set @out = replace(@out,'.','')
    END
    IF @isNegative = 1 BEGIN
        SET @out = '-' + @out
    end
 
    return (@out)
 
end
go
grant exec on f_4P_formattedQty3 to public

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences