• For what it's worth, here is a set-based approach. It requires a numbers table though.

    IF EXISTS ( SELECT  1
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[dbo].[CountDP]')
                        AND xtype IN ( N'FN', N'IF', N'TF' ) ) 
        BEGIN
            DROP FUNCTION [dbo].[CountDP]
        END
    GO
    
    CREATE FUNCTION [dbo].[CountDP]
        (
          @decNumber DECIMAL(18, 9) 
        )
    RETURNS TINYINT
    AS BEGIN
    /*******************************************************************************************************
    *   dbo.CountDP
    *
    *   Usage:
            print dbo.countdp(10.0000001) -- 7
            print dbo.countdp(10) -- 0
            print dbo.countdp(10.000) -- 0
            print dbo.countdp(0) -- 0
            print dbo.countdp(0.1234567) -- 7 
            print dbo.countdp(null) -- null
            print dbo.countdp() --ERROR
    
    *
    *   Modifications:   
    *   Developer Name      Date        Brief description
    *   ------------------- ----------- ------------------------------------------------------------
    *   
    ********************************************************************************************************/
    
        DECLARE @DecCount TINYINT
    
        SELECT  @DecCount = ( SELECT    ISNULL(MAX(num), N.Num)
                              FROM      Numbers
                              WHERE     Num  N.Num
                                        AND SUBSTRING(CAST(@decNumber AS VARCHAR),
                                                      Num, 1) NOT IN ( '0', '' )
                             ) - ( Num )
        FROM    Numbers N
        WHERE   Num < LEN(CAST(@decNumber AS VARCHAR(18)))
                AND SUBSTRING(CAST(@decNumber AS VARCHAR), Num, 1) = '.'
        RETURN @DecCount
    
       END
    GO
    

    SQL guy and Houston Magician