# Thinking outside the box

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

## Excel ERF clone for two variables

I previously posted a solution for one variable here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx and here is a solution for using two variables.

CREATE FUNCTION dbo.fnErf
(
@z1 FLOAT,
@z2 FLOAT,
@MaxIterations TINYINT = 10
)
RETURNS FLOAT
AS
BEGIN
IF @z1 IS NULL OR @z2 IS NULL
RETURN      NULL

DECLARE     @n TINYINT,
@s1 FLOAT,
@s2 FLOAT,
@p1 FLOAT,
@p2 FLOAT,
@a1 FLOAT,
@a2 FLOAT

SELECT      @n = 1,
@p1 = 1,
@p2 = 1,
@a1 = @z1,
@a2 = @z2,
@MaxIterations = COALESCE(ABS(@MaxIterations), 10)

WHILE @p1 <> 0.0E AND @p2 <> 0.0E AND @n <= @MaxIterations
SELECT      @s1 = @z1 /(2.0E * @n + 1.0E),
@s2 = @z2 /(2.0E * @n + 1.0E),
@p1 = - @p1 * @z1 * @z1 / @n,
@p2 = - @p2 * @z2 * @z2 / @n,
@a1 = @a1 + @s1 * @p1,
@a2 = @a2 + @s2 * @p2,
@n = @n + 1

RETURN      (@a1 - @a2) * 2.0E / SQRT(PI())
END

Print | posted on Wednesday, March 11, 2009 2:55 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.

Powered by: