## Excel ERF clone

This Excel ERF clone works for with SQL Server 2000 and later. I have found that 10 iterations will give enough acccuracy (maximum float accuracy) in most cases, so you can call the function with

SELECT dbo.fnErf(0.35, DEFAULT)

However, if you feel the need to more accuracy, replace second parameter with a value of your choice.

Here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone-for-two-variables.aspx is also a solution for two variables.

CREATE FUNCTION dbo.fnErf

(

@z FLOAT,

@MaxIterations TINYINT = 10

)

RETURNS FLOAT

AS

BEGIN

IF @z IS NULL

RETURN NULL

DECLARE @n TINYINT,

@s FLOAT,

@p FLOAT,

@a FLOAT

SELECT @n = 1,

@p = 1,

@a = @z,

@MaxIterations = COALESCE(ABS(@MaxIterations), 10)

WHILE @p <> 0.0E AND @n <= @MaxIterations

SELECT @s = @z /(2.0E * @n + 1.0E),

@p = - @p * @z * @z / @n,

@a = @a + @s * @p,

@n = @n + 1

RETURN @a * 2.0E / SQRT(PI())

END

Print | posted on Wednesday, March 11, 2009 1:59 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

## Feedback

## # re: Excel ERF clone

Is it an implementation of error function as described http://en.wikipedia.org/wiki/Error_function ?