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