Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Gravatar

# re: Excel ERF clone

Is it an implementation of error function as described http://en.wikipedia.org/wiki/Error_function ?
3/11/2009 3:45 PM | Freelance DBA
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET